I have a database server (Sql Server 2008 R2 Enterprise) supporting two separate but related vendor products, each with it's own database (call them X and Y)

In database X I a have a lot of users defined, but their access rights come down to a manageable handful of roles. I'd like to take one of these roles and use it to grant select access to a few tables in database Y. I really want to avoid creating a new role in database Y and re-adding all the users, not just because of the busy-work setting up that security info, but also because of needing to keep these roles in sync over time, as users come and go. Really, the role that already exists in database X already does a great job of logically defining the access group I want in database Y from a business standpoint, and I want to keep it that way.

How can I grant access in Y for this role from X?

3 Answers 3


Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases. So any roles and users that are in database X have no knowledge of database Y. To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and role. Fortunately, you can script out this process using the system views:

USE [Y];

--grant all perms on foo

--Use this generate sql to add all your users to the role
select 'CREATE USER '+quotename(u.name)+';' + char(10)+
       'EXEC sp_addrolemember ''foo'','''+u.name+''';'
from (select name,principal_id
        from x.sys.database_principals where type = 'R') r
    join x.sys.database_role_members rm 
        on (r.principal_id = rm.role_principal_id)
    join (select name,type_desc,principal_id
        from x.sys.database_principals where type != 'R') u 
        on (rm.member_principal_id = u.principal_id )
where r.name = 'foo'

You can then take this sort of script/process to a batch job to keep things synchronized, but you will need some sort of external process to keep these roles in sync.

One piece that might be confusing here is that you have logins and other server level principals that are connected to the database principals, but there is a clear distinction between these. I provide some explanation about the differences between these principals in this answer

  • I have a SP you might consider using as well that will script out the users assigned to the role, the role, and the roles permissions. sqlstudies.com/free-scripts/sp_dbpermissions Commented Jan 22, 2014 at 17:39
  • This is less than ideal :( It'd definitely need a scheduled job to keep them in sync over time. What about a linked server that links back to the original database? Then I could just have one account that has rights to those tables in Y, and the linked db would always connect as that account. Then I could create views for the tables from the linked server and assign the role in X rights to select from the views. Performance would suffer (a lot!), but right now I'm just looking at 3 smallish tables (largest has 3000 rows). Commented Jan 22, 2014 at 17:56

If you work with Windows Authentication, you can add your domain users to a domain group and add this group as a login to SQL Server. Then give that login the desired permissions on the relevant databases.

  • Hmm... we do use windows authentication already, but this might work. Commented Jan 22, 2014 at 18:00
  • ... I don't know, though. I'm already counting on users having logins under different AD groups. Not sure how these will combine. I'll give it a shot, though. Commented Jan 22, 2014 at 18:01
  • Okay, this worked! It's less than idea, because I had to use a broader AD group than was strictly necessary, but it's better than having the info duped and potentially becoming stale or a maintenance issue. Commented Jan 22, 2014 at 18:17
  • Great, glad I could help Commented Jan 22, 2014 at 19:37

I'm doing this a new way now.

I set up views in database X that reference the tables I care about in Y, and then I granted SELECT permissions on those views to the role I care about in X.

This change was made a while ago, and I may have also needed some linked-server magic to handle the authentication/security into Y to avoid needing to duplicate permissions there. I don't remember. But today the view exists in X, and managing access to that view is a much simpler task.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.