Granting permissions to stored procedures

I am using the following code to grant user access to the stored procedures in my database. However, it does not appear to be working because I am getting an access denied message when running the application as the user.

Here is the code I am using:

Code Snippet

' Grant privileges

Dim ExecutePrivilege As New ObjectPermissionSet

ExecutePrivilege.Execute = True

' Grant privileges to all non-system stored procs

' The following line improves performance

SmoServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")

For Each sp As StoredProcedure In db.StoredProcedures

If Not sp.IsSystemObject Then

sp.Grant(ExecutePrivilege, loginName)

End If

Next

Is there something else I need to do, like a Save or Refresh or something? (I've stepped through the code and it *is* executing for each of my stored procedures. It just does not appear to actually have updated the priviledge.)

Any tips or ideas would be appreciated.

Thanks!

Security related commands are always executed directly, unless you change the setting from the context to only script the commands like the following statement does:

svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

The reflected sources show that a property is set for this which is called

so.ForDirectExecution = true;

Did you have a look in the profiler to see if the commands are arriving at the server and are eventually bounced back due to errors occuring during applying the script ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I skipped the profiler and went right to the database. I can view permissions, and they are actually set correctly. So the code is executing. (Should have thought of that before I posted!<G>)

The problem is that when I try to access any of the stored procs (or the tables), I get a "permission was denied on the object" message. So something else is obviously wrong.

This particular code happens to be in VB6, accessing SQLServer Express. When the *same* code accesses a SQL Server 2000 database, it runs without this error.

Any idea what could be wrong here? Or at this point do I need to move the question elsewhere since it does not appear to be an SMO problem.

sql

Granting EXECUTE permissions to all stored procedures

I want to allow my user to have exec permissions on all stored procs in the
database. Is there a quick way to do this? Right now, the only way I know ho
w to do this is to go into the permissions for the user and check the EXEC c
heckbox for each individual
stored proc...Hi
Execute the following in Query Analyzer with text result (Query menu click
result in text) and copy and paste results
to give you the required script.
select 'grant exec on ' + QUOTENAME(name) + ' to [user_name]'
from sysobjects where type = 'P'
and objectproperty(id,'IsMSShipped')=0
Note:
Replace the user_name with actual user name or role name.
Tahnks
Hari
MCDBA
"DBA72" <anonymous@.discussions.microsoft.com> wrote in message
news:77702A18-57F2-4B75-B6AC-B4D769DA8951@.microsoft.com...
> I want to allow my user to have exec permissions on all stored procs in
the database. Is there a quick way to do this? Right now, the only way I
know how to do this is to go into the permissions for the user and check the
EXEC checkbox for each individual stored proc...

Granting Edit Permission on Stored Procedures

I have a user on my database that has the following base permissions :

public
db_datareader

I need to give this user permission to edit a single stored procedure. I have tried using the following command :

GRANT ALL ON stored_procedure_name TO username

Which executes successfully, but the user still cannot edit the stored procedure.

If I give the user db_ddladmin permission they can edit all the user stored procedures, but for security reasons I would prefer to be able to this this at procedure level rather than a global permission on all user procs.

Does anybody know how I can do this?

EDIT : This is on SQL 2000Does this user have the same permission on some tables which related to this procedure?sql

Grant Win Acct Permission

I'd like to grant a WIndows account permission to connect to a db and
exec stored procedures. But am having trouble.

I want this type of effect but can't get the syntax correct:

USE MyDB
GO
CREATE USER 127.0.0.1\ASPNET --ASPNET Account for current machine
GO

GRANT EXECUTE ON AllStoredPRocs TO 127.0.0.1\ASPNET

How is this done in a t-sql script?

Thanks for any help.(wackyphill@.yahoo.com) writes:
> I'd like to grant a WIndows account permission to connect to a db and
> exec stored procedures. But am having trouble.
> I want this type of effect but can't get the syntax correct:
> USE MyDB
> GO
> CREATE USER 127.0.0.1\ASPNET --ASPNET Account for current machine
> GO
> GRANT EXECUTE ON AllStoredPRocs TO 127.0.0.1\ASPNET
> How is this done in a t-sql script?

Since 127.0.0.1\ASPNET is one identifier as far SQL Server is concerned,
you need to put it brackets: [127.0.0.1\ASPNET]. Then whether that
actually works is another matter. You should probably use machine name
rather than 127.0.0.1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Grant using Query

Hello,
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>

Grant permissions

Hi all,
Can I grant select only permission on all objects in the database? I have users that I need to give view access only on stored procedures, triggers, and functions. Thanks.db_datareader (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_6ndx.asp).

-PatPsql

GRANT Permission to Users

I need to grant EXEC permission to several users on my Procedures. I would
like to to do this in One GRANT statement. this doesn't run. What do I need
to change.
GRANT EXEC ON
--Proc_Clear_data_CAS_ODS
--Proc_Clear_data_CMS_ODS
--Proc_Clear_data_GAS_ODS
--Proc_load_data_ShipperDim
--Proc_load_data_capacityGrpDim
--Proc_load_data_CAS_To_ODS
--Proc_load_data_CMS_To_ODS
--Proc_load_data_ContractDim
--Proc_load_data_CMS_To_ODS
--Proc_load_data_GAS_To_ODS
--Proc_load_data_PayerDim
--Proc_load_data_pointGrpDim
--Proc_load_data_ShipperDim
TO USER 1
USER 2
USER 3
WITH GRANT OPTION
Hi,
You can not give multiple procedure names in a Grant statement. But the
otherway is possible,
Exexute previlage on a single procedure to multiple users.
Grant exec on proc1 to usr1,ur2,usr3
Thanks
Hari
MCDBA
"baaul" wrote:

> I need to grant EXEC permission to several users on my Procedures. I would
> like to to do this in One GRANT statement. this doesn't run. What do I need
> to change.
> GRANT EXEC ON
> --Proc_Clear_data_CAS_ODS
> --Proc_Clear_data_CMS_ODS
> --Proc_Clear_data_GAS_ODS
> --Proc_load_data_ShipperDim
> --Proc_load_data_capacityGrpDim
> --Proc_load_data_CAS_To_ODS
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_ContractDim
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_GAS_To_ODS
> --Proc_load_data_PayerDim
> --Proc_load_data_pointGrpDim
> --Proc_load_data_ShipperDim
> TO USER 1
> USER 2
> USER 3
> WITH GRANT OPTION

GRANT Permission to Users

I need to grant EXEC permission to several users on my Procedures. I would
like to to do this in One GRANT statement. this doesn't run. What do I need
to change.
GRANT EXEC ON
--Proc_Clear_data_CAS_ODS
--Proc_Clear_data_CMS_ODS
--Proc_Clear_data_GAS_ODS
--Proc_load_data_ShipperDim
--Proc_load_data_capacityGrpDim
--Proc_load_data_CAS_To_ODS
--Proc_load_data_CMS_To_ODS
--Proc_load_data_ContractDim
--Proc_load_data_CMS_To_ODS
--Proc_load_data_GAS_To_ODS
--Proc_load_data_PayerDim
--Proc_load_data_pointGrpDim
--Proc_load_data_ShipperDim
TO USER 1
USER 2
USER 3
WITH GRANT OPTIONHi,
You can not give multiple procedure names in a Grant statement. But the
otherway is possible,
Exexute previlage on a single procedure to multiple users.
Grant exec on proc1 to usr1,ur2,usr3
Thanks
Hari
MCDBA
"baaul" wrote:
> I need to grant EXEC permission to several users on my Procedures. I would
> like to to do this in One GRANT statement. this doesn't run. What do I need
> to change.
> GRANT EXEC ON
> --Proc_Clear_data_CAS_ODS
> --Proc_Clear_data_CMS_ODS
> --Proc_Clear_data_GAS_ODS
> --Proc_load_data_ShipperDim
> --Proc_load_data_capacityGrpDim
> --Proc_load_data_CAS_To_ODS
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_ContractDim
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_GAS_To_ODS
> --Proc_load_data_PayerDim
> --Proc_load_data_pointGrpDim
> --Proc_load_data_ShipperDim
> TO USER 1
> USER 2
> USER 3
> WITH GRANT OPTION

GRANT permission to lots of tables and sp to db user

Is it possible to grant permissions (select, insert, delete, update, exec)
to a db user to all tables and all stored procedures in a specific db in an
easy (lazy!) way?
I mean, except for clicking in all permission checkboxes in Enterprise
Manager or writing a huge sql script like
grant select, insert, delete, update
on mytable1
to myuser
grant select, insert, delete, update
on mytable2
to myuser
...
grant exec
on mySP1
to myuser
grant exec
on mySP2
to myuser
...
?
Is there another way, like
GRANT select, insert, delete, update
on AllMyTables
to myuser
GRANT exec
on AllmySP
to myuser
?You can use a script like to example below to grant mass permissions
according to your requirements.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(500)
DECLARE @.LastError int
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
N'GRANT ' +
CASE
WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
N'SELECT, INSERT, UPDATE, DELETE'
WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
N'SELECT'
WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
N'EXECUTE'
END +
N' ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[nam
e]) +
N' TO MyRole'
FROM
sysobjects ob
WHERE
OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
(OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Siri" <Siri@.discussions.microsoft.com> wrote in message
news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@.microsoft.com...
> Is it possible to grant permissions (select, insert, delete, update, exec)
> to a db user to all tables and all stored procedures in a specific db in
> an
> easy (lazy!) way?
> I mean, except for clicking in all permission checkboxes in Enterprise
> Manager or writing a huge sql script like
> grant select, insert, delete, update
> on mytable1
> to myuser
> grant select, insert, delete, update
> on mytable2
> to myuser
> ...
> grant exec
> on mySP1
> to myuser
> grant exec
> on mySP2
> to myuser
> ...
> ?
> Is there another way, like
> GRANT select, insert, delete, update
> on AllMyTables
> to myuser
> GRANT exec
> on AllmySP
> to myuser
> ?
>|||Thank you very much! This really helped!
Siri
"Dan Guzman" wrote:

> You can use a script like to example below to grant mass permissions
> according to your requirements.
> SET NOCOUNT ON
> DECLARE @.GrantStatement nvarchar(500)
> DECLARE @.LastError int
> DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> SELECT
> N'GRANT ' +
> CASE
> WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
> N'SELECT, INSERT, UPDATE, DELETE'
> WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
> N'SELECT'
> WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
> N'EXECUTE'
> END +
> N' ON ' +
> QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].&#
91;name]) +
> N' TO MyRole'
> FROM
> sysobjects ob
> WHERE
> OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> OPEN GrantStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> EXECUTE sp_ExecuteSQL @.GrantStatement
> END
> CLOSE GrantStatements
> DEALLOCATE GrantStatements
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Siri" <Siri@.discussions.microsoft.com> wrote in message
> news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@.microsoft.com...
>
>

Grant Execute to user on procedures

Hi,
is there a easy way of grantinng Execute-privilegies to user ABC for all
procedures named 'MTS*' in a database?
regards,
Bent S. Lund
System Developer
MCP VB
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Hi,
In Query Analyzer execute the below script with Text result.
Use <dbname>
go
select 'grant execute on '+name +' to ABC' from sysobjects where name like
'MTS%' and type='P'
-- The above script will generate a script to grant execute previlage to ABC
user for all procedures start with MTS%.
Copy the result window and paste in a new Query analyzer window and execute
it.
Thanks
Hari
MCDBA
"Bent Lund" <bstlu@.online.no> wrote in message
news:ucjryKQYEHA.3012@.tk2msftngp13.phx.gbl...
> Hi,
> is there a easy way of grantinng Execute-privilegies to user ABC for all
> procedures named 'MTS*' in a database?
>
> regards,
> Bent S. Lund
> System Developer
> MCP VB
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Have a look at
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
and sp_grantexec
http://www.sqldbatips.com/showcode.asp?ID=2
You can use this like
exec sp_grantexec 'ABC','MTS%'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bent Lund" <bstlu@.online.no> wrote in message
news:ucjryKQYEHA.3012@.tk2msftngp13.phx.gbl...
> Hi,
> is there a easy way of grantinng Execute-privilegies to user ABC for all
> procedures named 'MTS*' in a database?
>
> regards,
> Bent S. Lund
> System Developer
> MCP VB
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Grant EXEC to SPROCS

I want to grant exec permission to a windows account <domain\user> to 150
stored procedures that are prefixed with usp_ . How do I script this so that
i won't have to do them individually?
>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually
Try a script like the one below.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1 AND
ROUTINE_NAME LIKE 'usp[_]'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:FAD323C1-40A9-46C6-B9EC-F02CFDF3E4B7@.microsoft.com...
>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually?
sql

Grant EXEC to SPROCS

I want to grant exec permission to a windows account <domain\user> to 150
stored procedures that are prefixed with usp_ . How do I script this so that
i won't have to do them individually?>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually
Try a script like the one below.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1 AND
ROUTINE_NAME LIKE 'usp[_]'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:FAD323C1-40A9-46C6-B9EC-F02CFDF3E4B7@.microsoft.com...
>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually?

Grant Exec to all UDFs and Stored Procedures

Hi All,
I am little at a loss here. I found a procedure that allows me to
pass two parameters: username and dbname and it grants execute to that
user to all stored procedures on theat specific database.
I couldn't find anything that would allow me to grant execute
permissions on all UDFs as well for a specific user.
Is there anything that I can use?
thank you,
T.Same as you do for a stored procedure. you can grant execute permission only
on scalar udfs.
grant execute on <schema.udf_name> to <database_principal>
AMB
"tolcis" wrote:
> Hi All,
> I am little at a loss here. I found a procedure that allows me to
> pass two parameters: username and dbname and it grants execute to that
> user to all stored procedures on theat specific database.
> I couldn't find anything that would allow me to grant execute
> permissions on all UDFs as well for a specific user.
> Is there anything that I can use?
> thank you,
> T.
>

Grant ASPNET all access

hello all,

I would like to grant the MACHINENAME\ASPNET user all acccess to my MSSQL database (i.e tables and stored procedures. I can do this through enterprise manager but id rather do it programatically so i can add it to my database creation script. What is the syntax for this?

I thought it would be along the lines of:

Grant all on databasename to localhost\ASPNET.

But obviously its not! Please help!

thanks

TomIn response to my own question, if anyone needs to know:

exec sp_grantlogin N'machine-name\ASPNET'
go
exec sp_grantlogin N'NT AUTHORITY\NETWORK SERVICE'
go
use dbname
go
exec sp_grantdbaccess
@.loginame = 'machine-name\ASPNET'
go
exec sp_grantdbaccess
@.loginame ='NT AUTHORITY\NETWORK SERVICE'
go

(Rename machinename and dbname to your specifics)

The network service user is required just for windows 2003 i believe, i think on xp and 2000 only ASPNET is needed access.

Tom

Grant alter procedure kind of thing

Hey guys. I need to let a developer alter procedures but not create any new
procedures. Is there a way i can do it?
I don't want to
grant create procedure to accountName
Instead I want to
grant alter procedure to accountName
Please let me know if it's Possible. Thank You.Tejas Parikh,
You can check BOL and see the permissions for both statements. The "alter
procedure" permission is not transferable and just members of symin,
db_owner, db_ddladmin and the sp owner have permission to alter the sp.
AMB
"Tejas Parikh" wrote:

> Hey guys. I need to let a developer alter procedures but not create any n
ew
> procedures. Is there a way i can do it?
> I don't want to
> grant create procedure to accountName
> Instead I want to
> grant alter procedure to accountName
> Please let me know if it's Possible. Thank You.
>|||>> I need to let a developer alter procedures but not create any new
procedures.<<
What the heck'!!! Let's give all the teenagers car keys and whiskey.|||LOL... are you saying the programmers are bad?
Grant
Who gives a {censored} if I am wrong.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>
More like giving them car keys, whiskey and condoms. They may be driving
drunk, but at least . . ..
Truly that's a bad idea. If your developers are qualified to write stored
procedures, then you should let them decide how to structure the code and
add procedures as necessary.
David|||Thanks Alejandro and David for your reply. It gives me the answer. All, I wa
s
trying to say is I dont want them to add any more sp's, just alter them if
needed. But well, u have a point, David.
Thank you for all your help.|||This really does show just out of touch you are with out SQL Server is used
within industry.
Do you think every shop has a DBA writing database designs and stored
procedures?
Seriously, stop what you are doing and go and get a job as a junior
programmer and get some very needed industrial experience, it looks like you
are too class room bound and have little if any (probably the latter)
exposure to business.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144873426.107509.93190@.u72g2000cwu.googlegroups.com...
> procedures.<<
> What the heck'!!! Let's give all the teenagers car keys and whiskey.
>|||No, but like teenagers, HIGHLY SUSPECT! I have been both in my
lifetime, so I now these things :)|||>> More like giving them car keys, whiskey and condoms. They may be driving
drunk, but at least . . ..<<
As the adoptive father of "troubled teenagers" and the legal
grandfather of two bastards, they forget the condom when they are in a
hurry. I am not going to put a :) on that one. Much like constraints
on a database, I should have given them birth control shots ...|||>> Do you think every shop has a DBA writing database designs and stored pro
cedures? <<
NO! I assume that bad programmers, like you, are writing schemas and
stored procedures. A large part of my consulting is based on cleaning
up the mess.
I would hope that a GOOD shop has code reviews and teaches the novice
programmers how to write SQL.
My publishers would not like that :)
My first full-time paid programing job was in 1965; I was a GS-1 at the
Pittman-Dunn Research Labs in Philadelphia. When did you start on your
full-time paid programing job? You never worked your way from "code
monkey" to "guru", did you?
I will not be in a classroom again until April. I have a two w gig
for a Seimens company in South America. Do you ever leave the UK?
Leave your own company? Your own department within the company? Your
own team within that department?

Got Microsoft OLE DB Provider for ODBC Drivers error '80004005' when running Stored Procedures

when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help

Microsoft OLE DB Provider for ODBC Drivers error

'80004005'

[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from

TDS data stream

sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server

or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error

Many thanks, Please help, appreciated

Can you post more information about the configuration:

- What version of SQL Server do you use?

- What OLEDB provider do you use (e.g., SQLOLEDB, SQL Native Client?)?

- What is the definition of relevan tables?

- Code for the stored procedure and for the application calling it (omit any confidential information)?

|||

Thanks Peter:

it's SQL Server 2000 sp4, this problem happens on the OLEDB provider ODBC driver. the detials of sproc is that there is one sproc calling other 4 different sprocs to update 4 different table (about 30,000 columns need to be updated in each table). and it will run only once a month automaticly by Job.

I also tried to run those 4 sprocs separately and manually, still get the same problem, somehow.

cheers

|||anyone can help?|||

Could be a network performance or configuration error.

http://support.microsoft.com/default.aspx/kb/176256

Got Microsoft OLE DB Provider for ODBC Drivers error '80004005' when running Stored Procedures

when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help

Microsoft OLE DB Provider for ODBC Drivers error

'80004005'

[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from

TDS data stream

sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server

or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error

Many thanks, Please help, appreciated

Can you post more information about the configuration:

- What version of SQL Server do you use?

- What OLEDB provider do you use (e.g., SQLOLEDB, SQL Native Client?)?

- What is the definition of relevan tables?

- Code for the stored procedure and for the application calling it (omit any confidential information)?

|||

Thanks Peter:

it's SQL Server 2000 sp4, this problem happens on the OLEDB provider ODBC driver. the detials of sproc is that there is one sproc calling other 4 different sprocs to update 4 different table (about 30,000 columns need to be updated in each table). and it will run only once a month automaticly by Job.

I also tried to run those 4 sprocs separately and manually, still get the same problem, somehow.

cheers

|||anyone can help?|||

Could be a network performance or configuration error.

http://support.microsoft.com/default.aspx/kb/176256

global temp table vs. permanent table use

I need to decide what is better to use: global temp table ( I can't use local one) or permanent table in SQL 2000 stored procedures. I extract data from linked server table and update several tables on our server.
Those procedures scheduled to run every 3 hours.

Another question: for some reasons when I used global temp table, I wasn't able to schedule multi steps with every step executing one of the stored procedures.I think global temp tables should be visible to other stored procedures, right?

Your suggestions?Only if it's created in a driver sproc, then it calls nested sprocs.

Otherwise it's gone at the end of the process...

I'd say create a permanent one...you won't incurr the overhead of using tempdb...

Sounds like a staging table to me anyway...

Global search & replace stored procedure

I am trying to work with a rather large SQL server database with a few hundred stored procedures that I inherited. In trying to understand the structure, would like to be able to search the SP text globally, and then be able to rename variables and otherwise edit the whole set of SPs will global string search/replaces (refactor).

In SQL studio, or VS 2005 all i can can do is open one SP at a time.

Hi,

there is no refactoring tool in SQL Server 2005, AFAIK. You can serahc all procedure be querying the INFORMATION_SCHEMA.Routines for finding your called stored procedures. But keep in mind that of you *refactore* those queries, eventually client calls to that stored procedures referencing vertain variables could break.

SELECT Routine_NAME , Routine_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE Routine_definition LIKE '%SOMESeachValue%'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

GK - Prevent access to Stored Procedures

Hi to all,
In our environment we are strictly limiting the users to get access on what
the don' t need ( application users and developers have their own role ( and
are granted only on the things the need to access) ).
When one of them logs in he can only see his own objects but he will be able
to access to all STORED PROCEDURES there are. This means that whoever has t
he Management Console installed ( or use a thirth party application ) can a
ccess ( alter/delete ) stro
red procedures ( and jobs ( see other post in this forum ))
How to prevent this'
Thanx in advance,
GKramerThere is currently not a way in Enterprise Manager to prevent users from
viewing stored procedures.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand,
I'm an experianced Oracle DBA and wonder why SQL server is not providing opt
imal security ?
I hope Yukon provides a better security system where you can access you own
objects and grant others too ( or revoke them for having access ).
GKramer
The Netherlands