This is a small proc that will parse out the grant statements on user defined stored procedures, views and tables. This is a quick way to give a user rights in one quick sweep of the db. It can achieve this by leveraging the information stored in the meta data tables with SQL server.
Example syntax:
exec GrantRights ‘SomeUser’.
Here is the proc.
ALTER PROCEDURE [dbo].[GrantRights]
@username varchar(100)
AS
BEGIN
DECLARE @sqlstatement varchar(1000)
DECLARE @procname varchar(1000)
DECLARE @tablename varchar(1000)
–DO THE STORED PROCEDURES
declare proccursor cursor forward_only
for Select name from sysobjects WHERE xtype = ‘P’
open proccursor
while (1=1)
begin
fetch next from proccursor into @procname
if @@fetch_status 0
break;
SET @sqlstatement = ‘GRANT CONTROL, EXECUTE, TAKE OWNERSHIP, VIEW DEFINITION ON ‘ + @procname + ‘ TO ‘ + @username
print @sqlstatement
exec ( @sqlstatement )
end
close proccursor
deallocate proccursor
—NOW GO AND DO THE TABLES
declare tablecursor cursor forward_only
for Select name from sysobjects WHERE xtype = ‘U’
open tablecursor
while (1=1)
begin
fetch next from tablecursor into @tablename
if @@fetch_status 0
break;
SET @sqlstatement = ‘GRANT INSERT, UPDATE, DELETE, SELECT ON ‘ + @tablename + ‘ TO ‘ + @username
print @sqlstatement
exec ( @sqlstatement )
end
close tablecursor
deallocate tablecursor
END