June 4, 2008 at 9:10 am
I am trying to create a sql account called SQLRO and assign it a datareader role. I have this built inside a cursor to be able to loop through the entire database on the server. During execution I am getting this error message Msg 15023, Level 16, State 1, Line 1. I have included my script and the error message beklow.
--Script ran on sql server
declare @sql varchar(max)
declare @dbName sysname
DECLARE @account SYSNAME
DECLARE @VHO_Name nvarchar(15)
select @VHO_Name = LEFT(@@servername, 15)
SELECT @account = @VHO_Name + N'\SQLRO'
DECLARE db_cursor CURSOR FOR
select name from sys.databases where database_id > 4 and name <> 'Distribution'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName
WHILE @@FETCH_STATUS = 0
begin
set @sql = 'use '+@dbname
print @sql
exec (@sql)
set @sql = ('CREATE USER [' + @account + '] FOR LOGIN ['+@VHO_Name+'\SQLRO]; ')
exec (@sql)
print @sql
set @sql = ('sp_addrolemember ''db_datareader'', ''' +@VHO_Name+ '\SQLRO''')
print @sql
exec (@sql)
FETCH NEXT FROM db_cursor INTO @dbName
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
----
Error messages I am getting
use AdventureWorksDW
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
use AdventureWorks
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'TXCDTLT01HO2451\SQLRO' already exists in the current database.
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
use BranchDB
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'TXCDTLT01HO2451\SQLRO' already exists in the current database.
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
use ServiceGroupDB
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'TXCDTLT01HO2451\SQLRO' already exists in the current database.
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
use MyAdventureWorks
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'TXCDTLT01HO2451\SQLRO' already exists in the current database.
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
use epg
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'TXCDTLT01HO2451\SQLRO' already exists in the current database.
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
use epgpub
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'TXCDTLT01HO2451\SQLRO' already exists in the current database.
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
use BranchDB2
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'TXCDTLT01HO2451\SQLRO' already exists in the current database.
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
use tmspub
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'TXCDTLT01HO2451\SQLRO' already exists in the current database.
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
use ppvpub
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'TXCDTLT01HO2451\SQLRO' already exists in the current database.
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
use epgcache
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'TXCDTLT01HO2451\SQLRO' already exists in the current database.
CREATE USER [TXCDTLT01HO2451\SQLRO] FOR LOGIN [TXCDTLT01HO2451\SQLRO];
sp_addrolemember 'db_datareader', 'TXCDTLT01HO2451\SQLRO'
June 4, 2008 at 9:19 am
Are these databases that have been restored from another server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 4, 2008 at 9:29 am
I believe you need to combine the 'use '+@dbname with the rest of your @sql variable and execute it all at once with only one exec(@sql) statement.
As it stands now, you are printing @sql each time, and thinking that you are executing in the context of a different database each time, but in reality you are continually using AdventureWorksDW. Notice how you only start failing after the first addrole is successful in AdventureWorksDW context.
At least, this is the problem I have run into everytime I try to use a cursor looping through DB's and assigning that to 'USE @dbname'.
"Got no time for the jibba jabba!"
-B.A. Baracus
June 4, 2008 at 9:42 am
Good catch. I did not completely read the script. I asked about restores because I have seen this when the login is not on the server you restored to as the database still has the database user.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply