SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Which system tables save permissions and SQL server configuration


Which system tables save permissions and SQL server configuration

Author
Message
ForumUser3
ForumUser3
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 113
I need to add rights and permissions data to SQL source control. Which system tables from master/model/msdb do I need to link to source control in SQL Server 2008 and 2012.
I also want to save data related to SQL server configuration like Cost Threshold for parallelism and Max Degree of Parallelism in SQL source control, so that I can track changes. Which tables do I need to link to source control.

Thank You.

Forum UserCool
free_mascot
free_mascot
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3979 Visits: 2235
You can get the server configuration information by executing:
sp_configure

Other users and logins information can be obtained from catalogue views like sys.syslogins, sys.sysusers etc.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87145 Visits: 45267
Configuration information is in sys.configurations.

Logins are in sys.server_principals (sys.syslogins is deprecated, should not be used)
Database users are in sys.database_principals (sys.sysusers is deprecated, should not be used)
Permissions are in sys.server_permissions and sys.database_permissions, depending which you're looking for.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ForumUser3
ForumUser3
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 113
These are views, how do I get the tables used in the views, I don't see a script view option for these views.
Moreover, I was not able to see rights granted to a certain windows user. For example, we have 6 windows users who are part of a group (DevGroup). How do query to see what rights are granted to that group or 6 users on that group, on each of the databases on a server. I want to save that in my source code control, and compare with that in case the rights are changed, which happens very often.

sp_configure shows me the current configuration, which I will have to take a snapshot for future comparisons. Is there a way I can save data from certain tables that it pulls from , to my source control using Redgate tools. I do that for all the reference tables in our application, so we can easily compare if anything changes, and also keep a history of the changes.

Forum UserCool
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87145 Visits: 45267
You don't get at the tables. They're hidden system tables, you can't even see them let alone query them. Use the catalog views, that's what they are there for.
If you want the table-equivalent of sp_configure, it's sys.configurations.

For the permissions granted to those windows users, you want sys.database_principals (for the users and their roles), sys.database_role_members and sys.database_permissions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search