Add permissions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Add permissions

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2006-10-21 : 10:22:12

Originally posted this in wrong forumn:
Question:
have a bunch of sp's I need to push out to the server. I need to grant permissions to each so that the calling application will be able to execute them. I currently open each one, and manually add the user permission. Is there a script that i can run that will do this (there are over 100 sp's)
Thanks
Answer:
In Query Analyzer run the following and copy the result set and paste into another window and execute

select 'GRANT EXECUTE ON ''' + name + ''' TO PUBLIC'
from sysobjects
where xtype = 'P'
Question:
So if I have a specific user I want to use, do I replace PUBLIC with that user?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-21 : 10:23:42

Yes. You can also grant to multiple users by separating each name with commas.

Enterprise Manager's scripting function can include permissions. And if you're using source control (and you should be) you should include the grant statements in the script file.

ann
Posting Yak Master

220 Posts

Posted - 2006-10-21 : 10:29:00

Thanks - 1 more question, what does the name stand for (is that the name of the db)?

select 'GRANT EXECUTE ON ''' + name + ''' TO PUBLIC'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-21 : 11:29:15

name is the column from the sysobjects table, it will have the stored procedure names. You might want to use the following, it generates a fully-qualified statement:

SELECT 'GRANT EXECUTE ON ' + quotename(user_name(uid)) + '.' + quotename(name) + ' TO Public'
FROM sysobjects
WHERE xtype='P'

This will cover procedures with spaces, dashes, etc., in their names and the owner of the procedure.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-21 : 12:32:55

This code uses the INFORMATION_SCHEMA.ROUTINES view, and has a check to exclude Microsoft objects.


select
	[--Grant Permissions] =
	'grant execute on ['+
	ROUTINE_SCHEMA+'].['+ROUTINE_NAME+'] to MyRoleName'
from
	INFORMATION_SCHEMA.ROUTINES
where
	ROUTINE_TYPE = 'PROCEDURE' and
	-- Exclude Microsoft objects
	objectproperty(object_id('['+ROUTINE_SCHEMA+'].['+ROUTINE_NAME+']'),'IsMSShipped') <> 1
order by
	ROUTINE_SCHEMA,
	ROUTINE_NAME

Results:


--Grant Permissions
----------------------------------------------------------------------
grant execute on [dbo].[CustOrderHist] to MyRoleName
grant execute on [dbo].[CustOrdersDetail] to MyRoleName
grant execute on [dbo].[CustOrdersOrders] to MyRoleName
grant execute on [dbo].[Employee Sales by Country] to MyRoleName
grant execute on [dbo].[Sales by Year] to MyRoleName
grant execute on [dbo].[SalesByCategory] to MyRoleName
grant execute on [dbo].[Ten Most Expensive Products] to MyRoleName

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources