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
Here's what I came up with -- thanks to @CL. for the tips. I made it into a view so I can just query against that for a particular user.
CREATE VIEW user_permissions_deep AS
SELECT DISTINCT user_id, permission_id
FROM (
SELECT urd.user_id user_id, pr.permission_id permission_id, urd.role_id role_id, urd.parent_id parent_role
FROM (
WITH RECURSIVE
deep_roles(user_id, role_id, parent_id, depth) AS (
SELECT ru.user_id, rr.role_id, rr.parent_id, 0 depth
FROM role_roles rr
INNER JOIN role_users ru ON rr.role_id = ru.role_id
UNION
SELECT dr.user_id, rr.role_id, rr.parent_id, dr.depth+1
FROM deep_roles dr, role_roles rr
WHERE rr.role_id=dr.parent_id
AND dr.depth < 10
)
SELECT user_id, role_id, parent_id FROM deep_roles
WHERE depth > 0
UNION
SELECT user_id, role_id, null parent_id
FROM role_users
) AS urd
INNER JOIN permission_roles pr
ON pr.role_id = urd.role_id
OR pr.role_id = urd.parent_id
UNION
SELECT user_id, permission_id, null role_id, null parent_id
FROM permission_users
) AS upd
INNER JOIN permission p ON upd.permission_id = p.id;
I found I had to keep the depth check otherwise the loop would never exit if there was a cycle in the role inheritance chain.
If anyone has any suggestions on how this could be improved, please let me know!
Best Answer
If you want every student get access only to the rows reguarding that sudent, you can accomplish this by adding one more field to your students table where for each student you'll be store his AD account, then create a view that will join all you need including your students table and using a filter like this
So every student will get access to his rows only. Here USER is a built-in T-SQL function that will return student's AD account. Certainly you should create logins for your students using windows authentication; you do not need to map them individually, it will be sufficient to add one windows group yourDom\STUDENTS where every student is a member of yourDom\STUDENTS