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.
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 |
|
|
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 |
|
|
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_idorrun in mastersp_helptext sp_helprotectand see how the SP does it KH |
|
|
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=0Where Users.IsLogin=1' Thank you |
|
|
|
|
|
|
|