Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Which system tables save permissions and SQL server configuration Expand / Collapse
Author
Message
Posted Monday, September 23, 2013 11:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 12:04 PM
Points: 33, 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 User
Post #1497481
Posted Tuesday, September 24, 2013 3:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1497740
Posted Tuesday, September 24, 2013 4:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 39,886, Visits: 36,233
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 2008, MVP
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

Post #1497744
Posted Tuesday, September 24, 2013 5:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 12:04 PM
Points: 33, 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 User
Post #1497778
Posted Tuesday, September 24, 2013 5:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 39,886, Visits: 36,233
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 2008, MVP
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

Post #1497781
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse