scripting DB permissions on a server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

scripting DB permissions on a server

Author

Topic

jstormoen
Starting Member

30 Posts

Posted - 2006-03-29 : 08:40:32

I need to grant DB access to 70 of the 80 DB's on a server. Is there a way to script this ? I have been having trouble as if I use a cursor of the DBnames and the code below the use dBname does not appear to work and if I put a go statement in after the USE the script will not parse. Any ideas ?

--EXEC ('USE ' + @inDBName)

--EXEC sp_grantdbaccess 'vcpi\magnolia-AHT', 'Magnolia'

--EXEC sp_addrolemember 'db_owner', 'Magnolia'

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-29 : 09:07:36

instead of go use ;
and use one exec statement
like ... stat1;stat2;stat3...

HTH

--------------------
keeping it simple...

jstormoen
Starting Member

30 Posts

Posted - 2006-03-29 : 09:24:04

This did not appear to work it still only added to the DB that is set when I am running the script

schuhtl
Posting Yak Master

102 Posts

Posted - 2006-03-29 : 09:33:46

Something like this should work for all of your databases (on the same server). I really have not tested this script that much so try it in your dev environment first!

DECLARE @DBName SYSNAME
DECLARE @cmd SYSNAME
DECLARE @RowCnt INT

CREATE TABLE #DbList
(
dbName SYSNAME NOT NULL
)

INSERT INTO #DbList (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'msdb', 'tempdb')
ORDER BY [name]

SELECT TOP 1 @DBName = dbName
FROM #DbList

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

SELECT @cmd = 'USE ' + quotename(@DBName, '[') + '; EXEC sp_grantdbaccess ''vcpi\magnolia-AHT'', ''Magnolia''; EXEC sp_addrolemember ''db_owner'', ''Magnolia'';'
PRINT @cmd
EXEC (@cmd)
DELETE
FROM #DbList
WHERE dbName = @DBName

SELECT TOP 1 @DBName = dbName
FROM #DbList

SET @RowCnt = @@ROWCOUNT

END

DROP TABLE #DbList

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-29 : 09:45:34

post your script so we can help you identify which part of your script is not working

--------------------
keeping it simple...

jstormoen
Starting Member

30 Posts

Posted - 2006-03-29 : 11:15:09

Thank You that script seemed to work - that is what I was looking for - couldn't figure out the way to use a specific database

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources