can table permissions be granted automatically?
-
i have a large number of stored procedures that will not run because the users do not have permissions on the tables.
is there a tool that will examine the stored procedures and automatically script a role that also has the correct select/insert/update/delete permissions for the tables?
thx
rov
-
Hmmm ... what are your stored procedures doing that the require specific permissions on the tables? Usually all you need to do is grant EXECUTE permission to the stored procedure. This is generally used as a security device to prevent people from modifying the tables directly and therefore bypassing any business rules that may be contained in the stored proc.
--------------------
Colt 45 - the original point and click interface
-
thanks for the post phil.
is this also the case for nt authentication?
rob
-
NT Authentication is used for connecting to the server. Once you have successfully connected to the server, the permissions from there on are assigned by SQL Server.
This is the distinction between logins and users. Logins exist at server level and if they're Windows logins, they relate directly to either the local or domain user/group. Users on the other hand can be in one or many databases and relate to a single login. Permissions to execute procedures and access tables are granted to users.
--------------------
Colt 45 - the original point and click interface -
We created this sproc to run about every 10 minutes in the development environment. We have naming standards so this will work for us. We only are granting "select" on the tables and "exec" on the sprocs/functions, but you could change this to whatever you want. It works well for us; it's just a matter of having the developers wait 10 minutes after they create a new sproc.
If you have specific naming conventions for your sprocs you could use something like this:
---------------BEGIN
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure [dbo].[spr_MAINT_GRANT_EXEC_Myrole]
AS
declare @routineName sysname
declare @dataType sysname
declare syscursor cursor for
select routine_schema + '.' + routine_name, data_type
from INFORMATION_SCHEMA.ROUTINES
where (routine_name like 'spr_%' or routine_name like 'fn_%')
and
(
(routine_name not like 'spr_MAINT%')
and (routine_name not like 'fn_MAINT%')
 
open syscursor
fetch next from syscursor into @routineName, @dataType
while @@fetch_status = 0
if @dataType = 'TABLE'
begin
execute('grant select on ' + @routineName + ' to MyRole')
fetch next from syscursor into @routineName, @dataType
end
else
begin
execute('grant execute on ' + @routineName + ' to MyRole')
fetch next from syscursor into @routineName, @dataType
end
fetch next from syscursor into @routineName, @dataType
close syscursor
deallocate syscursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--------------------------END
Good Luck
-
Sorry about that Icon that was inserted; please ignore. I don't recall clicking on any of those things.
-
Try this undocumented stored procedure. This will grant SELECT on each table for a given user.
exec sp_MSForEachTable 'GRANT SELECT ON ? TO MySQLUser'
Best to avoid cursors when you can use a WHILE loop. See http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=529 for an example.
G. Milner
-
"exec sp_MSForEachTable 'GRANT SELECT ON ? TO MySQLUser'"
How would you NOT grant permissions using this sproc? This undocumented sproc will grant select on every table, including sys tables.
I think Rob also wanted to grant exec to the sprocs as well.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply