How to script permissions?

I'm using SQL Server 2000. I have an application that uses three
databases. Currently, when a new user is added (Windows Integration),
I go to each database, create the login then go to each spoc and give
that user rights.
Is there a faster more efficient way to do this? For example, I have
maybe two or three scripts (for various permission levels) that I can
drop in a user name and it does everything?
Thanks,
BrettYou could probably use the GRANT statement and dynamic SQL to do the trick.
"brett" <account@.cygen.com> wrote in message
news:1146001887.339002.206390@.g10g2000cwb.googlegroups.com...
> I'm using SQL Server 2000. I have an application that uses three
> databases. Currently, when a new user is added (Windows Integration),
> I go to each database, create the login then go to each spoc and give
> that user rights.
> Is there a faster more efficient way to do this? For example, I have
> maybe two or three scripts (for various permission levels) that I can
> drop in a user name and it does everything?
> Thanks,
> Brett
>|||Or, add the user to a Windows Group, and give that Group permissions.|||The Windows Group sounds like a good solution.
To be more automated, I'd like to build this process into application
deployment. Are there any spocs I can run to setup a new user login
and then start assigned specific permissions to objects in a database?
Thanks,
Brett|||If you want the user to have execute permission to every proc you could
use this
--Grab all the procedures for the current DB
SELECT IDENTITY(INT,1,1) AS ID,
SPECIFIC_NAME
INTO #Procedurelist
FROM INFORMATION_SCHEMA.ROUTINES --Only Procs
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),
'IsMSShipped') =0
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY SPECIFIC_NAME
DECLARE
@.Loopid INT,
@.MaxId INT,
@.UserName VARCHAR(50)
--This is the user that will get the execute permissions
SELECT @.UserName = 'SomeUser'
--Grab start and end values for the loop
SELECT @.Loopid = 1,
@.MaxId = MAX(ID)
FROM #Procedurelist
DECLARE
@.SQL VARCHAR(500),
@.ProcName VARCHAR(400)
--This is where the loop starts
WHILE @.Loopid <= @.MaxId BEGIN
--grab the procedure name
SELECT @.ProcName = SPECIFIC_NAME
FROM #Procedurelist
WHERE ID = @.Loopid
--construct the statement
SELECT @.SQL = 'GRANT EXECUTE ON ' + @.ProcName + ' TO ' + @.UserName
PRINT (@.SQL) --change PRINT to EXECUTE if you want it to run
automatically
--increment counter
SET @.Loopid = @.Loopid + 1
END
--clean up
DROP TABLE #Procedurelist
if you need user defined functions also use this
http://sqlservercode.blogspot.com/2...or.html

Denis the SQL Menace
http://sqlservercode.blogspot.com/