October 28, 2015 at 2:16 pm
I have a script that adds rights to databases automatically. The script works fine for AD Accounts and Groups but does not work for SQL Accounts. When it runs I get an error stating that the "User or role '.....' does not exist in this database." When I run the sp_addrolemember command myself with the same parameters, it works fine.
Here's the script,
CREATE PROCEDURE [dbo].[addroles] @database varchar(500)
as
begin
declare @name varchar(500)
, @db_owner char(1)
, @db_datareader char(1)
, @db_datawriter char(1)
, @db_ddladmin char(1)
, @cmd_user nvarchar(500)
, @cmd_reader nvarchar(500)
, @cmd_writer nvarchar(500)
, @cmd_owner nvarchar(500)
, @cmd_admin nvarchar(500)
, @sql nvarchar(4000)
, @fulldb nvarchar(500)
, @count int
, @value int
, @paramdefinition nvarchar(500)
if OBJECT_ID('tempdb..#users', 'u') is not null
drop table #users
create table #users ( userid int identity
, name varchar(500)
, dbowner char(1)
, dbreader char(1)
, dbwriter char(1)
, dbadmin char(1) )
set @fulldb = 'testdb.dbo.tblRestore_' + @database
select @sql = N'insert into #users select * from ' + space(1) + @fulldb
exec sp_executesql @sql
set @value = @@rowcount
SET @COUNT=1
WHILE @COUNT<@VALUE
BEGIN
Select @name = name
, @db_datareader = dbreader
, @db_datawriter = dbwriter
, @db_ddladmin = dbadmin
, @db_owner = dbowner
from #users
where userid = @count
IF @DB_DATAREADER=1
BEGIN
set @cmd_reader='EXEC sp_addrolemember ''db_datareader'', N''' + @NAME + ''''
exec sp_executesql @cmd_reader
end
IF @DB_DATAWRITER=1
BEGIN
set @cmd_writer='EXEC sp_addrolemember ''db_datawriter'', N''' + @NAME +''''
exec sp_executesql @cmd_writer
END
IF @DB_OWNER=1
BEGIN
set @cmd_owner='EXEC sp_addrolemember ''db_owner'', N''' + @NAME +''''
exec sp_executesql @cmd_owner
END
IF @DB_DDLADMIN=1
BEGIN
set @cmd_admin='EXEC sp_addrolemember ''db_ddladmin'', N''' + @NAME +''''
exec sp_executesql @cmd_admin
END
SET @COUNT=@COUNT+1
end
END
October 28, 2015 at 2:22 pm
I figured it out. Rookie mistake.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply