Create a RO user for all DBs - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

Create a RO user for all DBs

Author

Topic

CanadaDBA

583 Posts

Posted - 2007-02-02 : 15:38:23

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

Canada DBA

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-02 : 15:58:35

[code]WHILE @@FETCH_STATUS = 0
BEGIN
exec ('
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 [/code]

CODO ERGO SUM

CanadaDBA

583 Posts

Posted - 2007-02-05 : 10:59:01

Thanks it solved the problem.

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?

Canada DBA

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-05 : 11:58:38

quote:
Originally posted by CanadaDBA

Thanks it solved the problem.

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?

Canada DBA

You can create roles only at the database level, not the server level.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources