May 21, 2012 at 6:42 pm
Hello,
I haven't done this in awhile and I think I am forgetting something.
I have a table of users in one database.
I need all the users in this table to have dbo access to another database.
I created a role in the second database (database2). I gave it dbo schema.
I then tried to assign all of the users in the usertable from database1 the role that I had created:
DECLARE @sql NVARCHAR(4000),
@user VARCHAR(100),
@role VARCHAR(100),
@DB sysname,
@DELIM VARCHAR(4),
@BigSQL nvarchar(4000);
set @role = 'MyNewrole'
set @user = 'NewUser'
set @DB = 'Database2'
set @DELIM = ''''
SET @sql = '
IF NOT EXISTS (SELECT *
FROM sys.database_principals
WHERE [name] = N'''+ @delim +@user +''''+ @delim +')
BEGIN
CREATE USER ' + @user + ' FOR LOGIN [' + @user + ']
END;
EXEC sp_addrolemember N''' + @delim +@role + ''''+@DELIM+', ''' +@DELIM+ @user +@DELIM+ ''';';
SET @BigSQL = 'USE ' + @db + '; EXEC sp_executesql N''' + @sql + '''';
PRINT(@BigSQL);
EXEC(@BigSQL);
I am getting an error:
Msg 15007, Level 16, State 1, Line 6
'NewUser' is not a valid login or you do not have permission.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'NewUser' does not exist in this database.
I'm sure that I have done something like this before. I'm sure I am forgetting something.
If anyone could point me in the right direction, I would appreciate it.
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
May 23, 2012 at 11:44 pm
You can use the method mentioned here:
How to transfer logins and passwords between instances of SQL Server
May 24, 2012 at 6:00 am
Suresh,
The link just opens a blank window.
I did figure it out though.
1) Use a trigger to get the username when it is inserted into the user table.
2) Take the username and create a login.
3) Then create a database user based on the new login in database2.
4) Apply a role to the new database user.
It had been awhile and this is a vendor application. However, after working on it and just looking at database security, I remembered the correct steps.
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply