Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Hmm... we do use windows authentication already, but this might work. –  Joel Coehoorn Jan 22 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. –  Joel Coehoorn Jan 22 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. –  Joel Coehoorn Jan 22 at 18:17
    
Great, glad I could help –  Matan Yungman Jan 22 at 19:37

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];
CREATE ROLE foo;

--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

share|improve 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 –  Kenneth Fisher Jan 22 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). –  Joel Coehoorn Jan 22 at 17:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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