September 23, 2013 at 11:44 am
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:cool:
September 24, 2013 at 3:56 am
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."
September 24, 2013 at 4:03 am
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
September 24, 2013 at 5:36 am
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:cool:
September 24, 2013 at 5:51 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply