February 27, 2012 at 12:50 am
hi all,
Is there any script to assign permission to objects(SP,view,function) by user's in all db's.
per example -i have a stored procedure spxxxxxxxx and i have 20 db's in a dbs server.
and i have a user ixxxxx.
wht i need is the script by which i can assign permission to user ixxxxx to use a spxxxxxxxx in all 20 db's.
any help is appreciated !!!
thanks in advance !!!
February 27, 2012 at 1:29 am
you just need to grant execute on the schema in question
so if everything is in the DBO schema you would do
GRANT EXECUTE ON SCHEMA::dbo TO ixxxxxx
you could also create a database role, and then do the above grant to the role. that way as and when people join/leave a company/department you add/remove them to the role instead of doing the grant/deny to each user
February 27, 2012 at 3:21 am
anthony.green (2/27/2012)
you just need to grant execute on the schema in questionso if everything is in the DBO schema you would do
GRANT EXECUTE ON SCHEMA::dbo TO ixxxxxx
you could also create a database role, and then do the above grant to the role. that way as and when people join/leave a company/department you add/remove them to the role instead of doing the grant/deny to each user
as i want to have a script to do this activity in all db of a dbs.so the script will ?
February 27, 2012 at 3:24 am
you will need to build in a link to the system catalogs for schemas and users as they will be different in each do and then pass the values in as variables and run the script multiple times in each db
February 27, 2012 at 3:30 am
anthony.green (2/27/2012)
you will need to build in a link to the system catalogs for schemas and users as they will be different in each do and then pass the values in as variables and run the script multiple times in each db
as i understand let me explain u once more
-i have a stored procedure spxxxxxxxx
i have a sql db login name ixxxxx
i have 20 db's in a instance or dbs.
how can i provide access to this particular stored procedure spxxxxxxxx to this particular login ixxxxx in a dbs.
the sp is on all db and the login is db level login.?can u just ping me the script.?
February 27, 2012 at 3:41 am
take a look at an undocumented function called sp_msforeachdb, this will allow you to execute the same query in all databases including master model msdb and tempdb so you will get an error in them unless you have created the SP in all system DB's as well.
February 27, 2012 at 4:12 am
anthony.green (2/27/2012)
take a look at an undocumented function called sp_msforeachdb, this will allow you to execute the same query in all databases including master model msdb and tempdb so you will get an error in them unless you have created the SP in all system DB's as well.
i was expecting this answer thanks i am working on it and will post the script once i am done.
February 27, 2012 at 5:34 am
Ivan Mohapatra (2/27/2012)
anthony.green (2/27/2012)
take a look at an undocumented function called sp_msforeachdb, this will allow you to execute the same query in all databases including master model msdb and tempdb so you will get an error in them unless you have created the SP in all system DB's as well.i was expecting this answer thanks i am working on it and will post the script once i am done.
i hope this below dynamic query works
DECLARE @grantExecute varchar(8000)
select @grantExecute = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?
GRANT EXECUTE ON [dbo].[Storedprocedurename] TO (loginname) END' EXEC sp_MSforeachdb @grantExecute
February 27, 2012 at 5:36 am
wrap the ? in [] then test it, looks ok to me
February 27, 2012 at 5:41 am
anthony.green (2/27/2012)
wrap the ? in [] then test it, looks ok to me
DECLARE @grantExecute varchar(8000)
select @grantExecute = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE [?]
GRANT EXECUTE ON [dbo].[Storedprocedurename] TO (loginname) END' EXEC sp_MSforeachdb @grantExecute
now is it ok
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply