SQL Server Performance Forum – Threads Archive

Create a RO user for all DBs

In SQL 2005, I want to create a RO_user with read only access rights to some of my databases. I wrote the folloiwng code but get an error in USE @db_name. Is there an easier way to create the user id and grant the RO access right to it? I have about 500 databases. USE [Master]
GO DECLARE @db_name nvarchar(50) DECLARE db_cursor CURSOR FOR
SELECT master.dbo.[name]
FROM sysdatabases
WHERE Substring(name,1,4) in (‘DB06’, ‘AC06’, ‘RE07’) OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db_name WHILE @@FETCH_STATUS = 0
BEGIN USE @db_name
CREATE USER [RO_user] FOR LOGIN [RO_user]
EXEC sp_addrolemember N’db_datareader’, N’RO_user’ FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
CanadaDBA

Create your statement as dynamic sql and then you can use "USE" appropriately. USE [Master]
GO DECLARE @db_name nvarchar(50)
, @DynSQL varchar(512) DECLARE db_cursor CURSOR FOR
SELECT master.dbo.[name]
FROM sysdatabases
WHERE Substring(name,1,4) in (‘DB06’, ‘AC06’, ‘RE07’) OPEN db_cursor FETCH NEXT FROM db_cursor INTO @db_name WHILE @@FETCH_STATUS = 0 BEGIN
SET @DynSQL = ‘ USE [‘ + @db_name + ‘] CREATE USER [RO_user] FOR LOGIN [RO_user] EXEC sp_addrolemember N”db_datareader”, N”RO_user”
‘ — EXEC (@DynSQL)
PRINT @DynSQL FETCH NEXT FROM db_cursor INTO @db_name END CLOSE db_cursor

Well said by Haywood, and you can take help of SQLCMD embed within a batch file to implement on x number of servers. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Thanks for the posts! Probably, The best way was to create a role with Read Only access to all DBs and then Add the user to it. Is it possible? CanadaDBA

Already there is a role in all dbs called db_databreader which is read only database role… Yes, you can add the user to it …that what Haywood script is doing…
MohammedU.
Moderator
SQL-Server-Performance.com

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya Well said by Haywood, and you can take help of SQLCMD embed within a batch file to implement on x number of servers. <hr height="1" noshade id="quote"></blockquote id="quote"> Heh, dynamic sql is easy once you’ve done it for a few years… <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I love sqlcmd, I’ve already written several batch utilities that take advantage of the paramater substituting that sqlcmd allows you to do. Very neat stuff. [8D]

I cannot resist using OSQL, ISQL and now SQLCMD, they are handy. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

One more similar one is ASCMD for analysis services…
http://msdn2.microsoft.com/en-us/library/ms365187.aspx
MohammedU.
Moderator
SQL-Server-Performance.com

need it to be said that you can create that user in modeldb so all databases created after that time will automaticaly have that user in that role ? Offcourse you should still execute the statement for the existing databases you want to.

But having the user access to Model database is not a that good practice and as a practice it is better to create logins with a script for any new database. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

That is correct.
There are downsides e.g. when a user is connected to modeldb, you cannot create another database because modeldb cannot be placed in exclusive mode.
I just mentioned it because the thread’s title says "all DBs" and when it’s structural this may avoid issues because the checklist may not be checked that well [:I]

Appreciate your suggestion, I believe for such security based tasks it is better to depend upon the manaul process for more visibility. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>