February 19, 2009 at 5:48 am
I need to give a user read only permission for a database and execute permission for all stored procs in the database. The stored procs have DML statements inside them.
Will stored procedure execution fail if user has only read rights on database?
Is it possible to implement this and how?
February 19, 2009 at 3:06 pm
It is completely possible provided the owner of stored procedures and the tables refrenced in it are same.
MJ
February 19, 2009 at 4:45 pm
anuragingle (2/19/2009)
Will stored procedure execution fail if user has only read rights on database?Is it possible to implement this and how?
granting execute on a stored procedure to a user does not require that user to have permissions to the underlying tables
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 19, 2009 at 10:01 pm
I added the user to db_datareader and gave him execute rights on specified sp......... seems to be working
February 20, 2009 at 3:26 am
anuragingle (2/19/2009)
I added the user to db_datareader and gave him execute rights on specified sp......... seems to be working
yes it would do, you dont need to give db_datareader and it will still work as long as they have execute permissions on the SP 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2009 at 8:41 am
I'hope that this script helps you!
-----------------------------------------------------------
DECLARE @Group varchar(100)
Set @Group = '[My group SQL] or [MY domain\My Group Win]'
declare @Type char(1),@Name varchar(100), @Owner varchar(100)
declare Lista cursor for
Select 'P', a.name , propietario = left( b.name,40) from sysobjects a , sysusers b where a.xtype='P' and a.uid = b.uid
Union ALL
Select 'V', a.name , propietario = left( b.name,40) from sysobjects a , sysusers b where a.xtype='V' and a.uid = b.uid
Union ALL
Select 'F', a.name , propietario = left( b.name,40) from sysobjects a , sysusers b where a.xtype='FN' and a.uid = b.uid
Union ALL
Select 'T', a.name , propietario = left( b.name,40) from sysobjects a , sysusers b where a.xtype='TF' and a.uid = b.uid
OPEN Lista
FETCH NEXT FROM Lista
Into @Type ,@Name , @Owner
WHILE @@FETCH_STATUS = 0
BEGIN
if (@Type ='P' or @Type ='F')
begin
exec('grant all privileges' + ' on ' + @Name + ' to ' + @Group)
if @Owner <> 'dbo'
exec('sp_changeobjectowner ' + @Name + ',dbo')
end
else
exec(' grant Select ' + ' on ' + @Name + ' to ' + @Group)
FETCH NEXT FROM Lista
Into @Type ,@Name , @Owner
END
CLOSE Lista
DEALLOCATE Lista
February 20, 2009 at 9:13 am
Perry Whittle (2/19/2009)
granting execute on a stored procedure to a user does not require that user to have permissions to the underlying tables
The only time you may run into problems with this is if the stored procedure references objects in a different schema and the user that has exec permissions does not have permissions to the referenced schema or the tables within that schema. In a scenario such as this, you would need to add permissions for your user.
Regards, Jim C
February 20, 2009 at 9:20 am
matias.diaz (2/20/2009)
I'hope that this script helps you!-----------------------------------------------------------
OPEN Lista
FETCH NEXT FROM Lista
Into @Type ,@Name , @Owner
WHILE @@FETCH_STATUS = 0
BEGIN
if (@Type ='P' or @Type ='F')
begin
exec('grant all privileges' + ' on ' + @Name + ' to ' + @Group)
if @Owner <> 'dbo'
exec('sp_changeobjectowner ' + @Name + ',dbo')
end
else
exec(' grant Select ' + ' on ' + @Name + ' to ' + @Group)
FETCH NEXT FROM Lista
Into @Type ,@Name , @Owner
END
CLOSE Lista
DEALLOCATE Lista
This does more than the OP asked for. He didn't say he wants users to be able to change or drop stored procedures.
Greg
February 20, 2009 at 12:08 pm
So what would be the ideal way to give permissions to developers. i want them to execute stored procedures, maybe create stored procedures (not drop them) also but make no other changes to other database objects like tables etc. Any suggestions?
February 20, 2009 at 12:35 pm
They way I do it is that I have all my sprocs scripted out. At the bottom of the script, after a GO statement, i have the permissions that are required for that sproc. This is difficult to do in a wholesale manner, but once its done, maintenance of sproc permissions is actually very easy. (assuming you maintain your sprocs in source control).
If you just want to apply permissions directly to the database, then you can do it one of two ways.
1. use the SSMS GUI.
2. write a script that will loop through all the user sprocs in your database and run
GRANT EXECUTE ON [stored_procedure] TO
for each of your sprocs.
Regards, Jim C
February 20, 2009 at 4:17 pm
Hi,
By giving EXEC permissions on the Stored procedure the DML statements would execute. You need not give update permissions to the table explicitly with the exception that if your table is involved in any dynamic SQL.
If the table is involved in dynamic sql within the Stored Procedure then you need to assign relevant permissions to those tables explicitly.
SQL Script to assign permissions to all Stored procedures in a database to a given user.
(Assuming the schema owner is dbo, if not then change it to be the one that you want.)
SELECT 'GRANT EXECUTE ON dbo.' + [name] + ' TO [ ]'
FROM sys.sysobjects (NOLOCK)
WHERE TYPE = 'P'
Execute the resullts of the above query on the database where you need assign the Execute permissions to SP's.
Thanks,
Amol
Amol Naik
February 21, 2009 at 6:19 am
thanks for the help eveyone
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply