system tables for user rights and sql server configuration

Which system tables in SQL server 2008 /SQL server 2012 have the following information:
1. User right/permissions on different databases/tables/sprocs?
2. Database instance configuration like Cost Threshold for parallelism and Max Degree of Parallelism
3. when user rights changed or configuration changed,, how to track when it was changed and who changed it.

I want to know so that I can link the data to our SQL source control and track any changes to rights, permissions and configuration.

Any help is highly appreciated.

Thanks.
patd1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Brent Ozar's sp_Blitz has a section that checks for changes to default settings.

http://www.brentozar.com/blitz/

It wont do everything you are asking for, and is only supported for versions 2005+ which might be an issue as you've only selected MS SQL Server - by default that is SQL 2000 since there are separate topic areas for SQL 2005 and SQL 2008. But in what it does do, it should catch much which can bite, and hint at how to do the other things you are after.

HTH
  David
0
patd1Author Commented:
Let me explain once again, what I am trying to accomplish. We have Redgate tools that we use to link data in certain reference tables from our application to our source control, so that we can compare if any changes were made to the reference data, and keep a history of the changes.

I think all the user rights and server configurations are stored in system tables.  I want to link those tables to our source control using Redgate, so that we can compare is any changes were made and also keep a history of the changes to configuration or user rights.


I found certain views (
sysusers, syspermissions, sys.server_principals, sys.database_principals, sys.server_permissions, sys.database_permissions) that i can use to see current cinfiguration etc) but, 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.
0
Scott PletcherSenior DBACommented:
>> but, how do I get the tables used in the views <<

MS won't necessarily provide the underlying table names to you.  The idea is to "black box" so you don't access them directly.

The best chance to get the names is to try:

EXEC sp_helptext 'sys.server_principals'

but it may not work for all views.  And, even when it does work, the revealed table names will of course not be documented in any way to explain what they contain.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

patd1Author Commented:
hmmm...
Is there another way to get an alert when configuration or user rights/permissions change?
may be an email alert?

Thanks.
0
patd1Author Commented:
Here is a sample of what I am looking for in the attached file.
How do i query catalog views to get this result?

Thank you so much for your help.
user-rights.xls
0
David ToddSenior DBACommented:
Hi,

Look up the sp_help_revlogin_2000_to_2005
http://support.microsoft.com/kb/246133

Since this can include roles, it should have the hints that you need.

>>
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
<<

Regards
  David
0
patd1Author Commented:
>>
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
<<

I dont have a table master.dbo.sysxlogins
0
patd1Author Commented:
The following sql gives me everything I need except the db name,
is there another view i can join to get this for all databases?

SELECT 
UName, 
Max(CASE RName WHEN 'db_owner' THEN 'Y' ELSE 'N' END) AS db_owner,
Max(CASE RName WHEN 'db_accessadmin ' THEN 'Y' ELSE 'N' END) AS db_accessadmin ,
Max(CASE RName WHEN 'db_securityadmin' THEN 'Y' ELSE 'N' END) AS db_securityadmin,
Max(CASE RName WHEN 'db_ddladmin' THEN 'Y' ELSE 'N' END) AS db_ddladmin,
Max(CASE RName WHEN 'db_datareader' THEN 'Y' ELSE 'N' END) AS db_datareader,
Max(CASE RName WHEN 'db_datawriter' THEN 'Y' ELSE 'N' END) AS db_datawriter,
Max(CASE RName WHEN 'db_denydatareader' THEN 'Y' ELSE 'N' END) AS db_denydatareader,
Max(CASE RName WHEN 'db_denydatawriter' THEN 'Y' ELSE 'N' END) AS db_denydatawriter
from (
select 
 b.name as UName, c.name as RName
from dbo.sysmembers a  
join dbo.sysusers b on a.memberuid = b.uid 
join dbo.sysusers c on a.groupuid = c.uid 
)s 
Group by 
UName
order by UName

Open in new window

0
David ToddSenior DBACommented:
Hi,

try sys.sql_logins, sys.server_role_members, sys.server_principals, sys.syslogins.

Or this snippet from http://support.microsoft.com/kb/918992
 
     SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

HTH
  David
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.