Wednesday, January 5, 2011

SQL SERVER CREATE READONLY USERS

/*********************************************************
Joe Kelly
2011-01-05 13:19:07.680

Add user to all non-system, writable, databases as READONLY

Input: db server username

*********************************************************/

SET NOCOUNT ON

DECLARE @userName VARCHAR (128) = 'UCSFMC\sosas'

DECLARE @dbList TABLE (ident INT IDENTITY (0, 1), dbName VARCHAR (128))
DECLARE @execString VARCHAR (128) = ''
, @aVeryLongString VARCHAR (MAX) = ''
, @lineFeed CHAR (4) = CHAR(10) + CHAR(13)
, @maxCount INT = 0
, @iter INT = 0

IF NOT EXISTS
(
SELECT name
FROM master.dbo.syslogins
WHERE name = @userName
)
BEGIN
SELECT 'Please create server login for: ' + @userName
RETURN
END

INSERT @dbList (dbName)
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('Master', 'Resource', 'TempDB', 'Model', 'MSDB', 'Distribution', 'ReportServer', 'ReportServerTempDB')
AND name NOT LIKE 'ASPState%'

SELECT @maxCount = @@ROWCOUNT

WHILE (@iter < @maxCount)
BEGIN
SELECT @aVeryLongString = 'USE ['
+ dbName
+ '] '
+ @lineFeed
+ 'GO '
+ @lineFeed
FROM @dbList
WHERE ident = @iter

SELECT @aVeryLongString += 'IF NOT EXISTS ( SELECT name FROM sysusers WHERE name = '''
+ @userName
+ ''') BEGIN EXEC sp_adduser '''
+ @userName
+ '''; END '
+ @lineFeed

SELECT @aVeryLongString += 'EXEC sp_addrolemember db_datareader, '''
+ @userName
+ '''; '
+ @lineFeed

SELECT @aVeryLongString += 'EXEC sp_addrolemember db_denydatawriter, '''
+ @userName
+ '''; '
+ @lineFeed

SELECT @aVeryLongString

SELECT @iter += 1
END

No comments: