Grant users from table in one database access to another database

• WebTechie

  SSChampion

  Points: 11405

  More actions

  May 21, 2012 at 6:42 pm

  #258511

  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.

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  May 23, 2012 at 11:44 pm

  #1492262

  You can use the method mentioned here:

  How to transfer logins and passwords between instances of SQL Server

• WebTechie

  SSChampion

  Points: 11405

  More actions

  May 24, 2012 at 6:00 am

  #1492450

  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.