Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 where are permissions, roles, accesses.... located

Author  Topic 

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-30 : 21:44:06
If i want to access the permissions, accesses, roles... that a user or an NT domain group has on a specefic database, where do I get them from, in which table and what columns?
Let s say: I need to check for the user: myDomain/myUser or for the NT group: myDomian/myGroup all the accesses, permissions, roles...etc that the user or the group has on a specefic database called myDatabase. Where is that information located please? Like all that stuff: read, right, create table, ...stored procedure...etc plus all the roles for the user.

Please I don t want to miss any information that the user ot the group has on the database. Let me know if there are any other columns or types of security that I need to check, because I don t want to miss any information for that user or group on the database.

P.S: The purspose is this:
For some reason, a freind changed the domain name during a wimdpws migration but the domain name was not chagned in sql server instance. So all users, groups, logins...etc remain with the old domain name in sql server. So I need to make a script to take all the users and groups to their allowed databases with the same permissions, roles..etc they had before with the old domain name and grant to them the same access and permissions plus all the roles except that the "granting" will be given to the user or group with the new domain name instead of the old domain name.

That s why I can afford losing any information :)
Thank you very much

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 21:50:16
sp_helprotect user


KH

Go to Top of Page

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-30 : 22:04:06
I beg you pardon.

How do i use that as part of my script that will scan automatically all users of the database?

Thanks
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 22:08:07
use cursor to pass in the user id and use dynamic sql exec() or sp_executesql to execute sp_protect user_id

or

run in master
sp_helptext sp_helprotect
and see how the SP does it



KH

Go to Top of Page

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-01-30 : 22:21:46
I have so far this script that gives me the list of users with their databases. I still need to twick it to get the permissions:

Exec sp_MSforeachdb 'Select ''?'',Users.Name Loginname, Isnull(Groups.Name,''NA'') MemberOf From ?..Sysusers Users
Left Outer Join ?..Sysmembers Members On Members.memberuid = Users.Uid
Left Outer Join ?..Sysusers Groups On Groups.Uid = Members.GroupUid And Groups.IsLogin=0
Where Users.IsLogin=1'

Thank you
Go to Top of Page
   

- Advertisement -