January 11, 2010 at 7:57 pm
I have created a automated job where i restore my backup on a different server on the first step.
In the next step
USE []
GO
-----------
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'X')
DROP USER [x]
GO
CREATE USER [x] FOR LOGIN [x]
GO
exec sp_grantdbaccess [X],[X]
go
EXEC sp_addrolemember N'db_datareader', N'X'
GO
------------
i always end up creating the user but it is not added to data reader role. i have to manually assign it after the restore. what am i doing wrong here? can anybody help me here?
Thanks
January 12, 2010 at 9:33 am
When I tried this in a test database, I got an error on sp_grantdbacces that said, 'User, group, or role 'x' already exists in the current database'. I think it's because CREATE USER grants connect permission to a user when it's created, making sp_gratdbaccess redundant. I guess that's why BOL says to avoid using the sp.
Anyway, since sp_grantdbaccess errors, the script never gets to the sp_addrolemember command and you end up having to add the user to the role manually.
This works:
USE
GO
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'x')
DROP USER [x]
GO
create user x for login [x] with default_schema = dbo
exec sp_addrolemember 'db_datareader', 'x'
Greg
January 12, 2010 at 11:59 am
Thanks Greg
It makes sense now why i had to do it manually.
I will try and post what happened.
Thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply