October 28, 2009 at 8:47 am
I have been asked to:
"A screen print from SQL Server showing those who have SA rights on the key system databases "
Anybody know a way I could accomplish this?
Is there an app out there that would satisy this request, or a query that would show all the SA rights?
October 28, 2009 at 8:54 am
i have this saved in my snippets from a similar post:
--Security List Admin rights
EXEC sp_MSForEachDB 'SELECT ''?'' AS [Database Name], su1.name AS [Database User Name], su2.name AS [Database Role]
FROM [?].sys.database_role_members r
INNER JOIN [?]..sysusers su1 ON su1.[uid] = r.member_principal_id
INNER JOIN [?]..sysusers su2 ON su2.[uid] = r.role_principal_id
WHERE su2.name IN(''db_owner'') AND su1.name NOT IN(''dbo'')'
this doesn't show who gets admin rights based on being part of the Builtin\Administrators, though, so it's not as complete as it could be.
Lowell
October 28, 2009 at 9:04 am
You may also have people who know the SA password.
Here's a script I got from somewhere that gives you a lot more info as well. Look at the sysadmin column in the first query result:
/*
Audit SQL Server user ID
Author Simon Facer
Date 01/04/2007
This script will generate an audit of SQL Server logins, as well
as a listing of the database user ID's and the SQL Server login
that each DB user ID maps to.
In the database user ID results, [Server Login] = '** Orphaned **'
indicates that there is no matching Server login.
This script was originally designed for SQL 2000, but works just as
well in SQL 2005.
*/
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
DROP TABLE ##Users
GO
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
DROP TABLE ##DBUsers
GO
-- ***************************************************************************
-- Always run this from master
USE master
-- ***************************************************************************
-- Declare local variables
DECLARE @DBName VARCHAR(40)
DECLARE @SQLCmd VARCHAR(1024)
-- ***************************************************************************
-- Get the SQL Server logins
SELECT sid,
loginname AS [Login Name],
dbname AS [Default Database],
CASE isntname
WHEN 1 THEN 'AD Login'
ELSE 'SQL Login'
END AS [Login Type],
CASE
WHEN isntgroup = 1 THEN 'AD Group'
WHEN isntuser = 1 THEN 'AD User'
ELSE ''
END AS [AD Login Type],
CASE sysadmin
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [sysadmin],
CASE [securityadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [securityadmin],
CASE [serveradmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [serveradmin],
CASE [setupadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [setupadmin],
CASE [processadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [processadmin],
CASE [diskadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [diskadmin],
CASE [dbcreator]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [dbcreator],
CASE [bulkadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [bulkadmin]
INTO ##Users
FROM dbo.syslogins
SELECT [Login Name],
[Default Database],
[Login Type],
[AD Login Type],
[sysadmin],
[securityadmin],
[serveradmin],
[setupadmin],
[processadmin],
[diskadmin],
[dbcreator],
[bulkadmin]
FROM ##Users
--ORDER BY [Login Type], [AD Login Type], [Login Name]
ORDER BY sysadmin desc, [AD Login Type], [Login Name] -- Order by SYSADMIN to find holes to plug
-- ***************************************************************************
-- Create the output table for the Database User ID's
CREATE TABLE ##DBUsers (
[Database] VARCHAR(64),
[Database User ID] VARCHAR(64),
[Server Login] VARCHAR(64),
[Database Role] VARCHAR(64))
-- ***************************************************************************
-- Declare a cursor to loop through all the databases on the server
DECLARE csrDB CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
and status <> 66048-- SQL2005 DB offline
and status <> 66056-- SQL2005 AdventureWorksDW DB offline
and status <> 528-- SQL2000 DB offline
-- ***************************************************************************
-- Open the cursor and get the first database name
OPEN csrDB
FETCH NEXT
FROM csrDB
INTO @DBName
-- ***************************************************************************
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- ***************************************************************************
--
SELECT @SQLCmd = 'INSERT ##DBUsers ' +
' SELECT ''' + @DBName + ''' AS [Database],' +
' su.[name] AS [Database User ID], ' +
' COALESCE (u.[Login Name], ''** Orphaned **'') AS [Server Login], ' +
' COALESCE (sug.name, ''Public'') AS [Database Role] ' +
' FROM [' + @DBName + '].[dbo].[sysusers] su' +
' LEFT OUTER JOIN ##Users u' +
' ON su.sid = u.sid' +
' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' +
' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' +
' ON sm.groupuid = sug.uid)' +
' ON su.uid = sm.memberuid ' +
' WHERE su.hasdbaccess = 1' +
' AND su.[name] != ''dbo'' '
EXEC (@SQLCmd)
-- ***************************************************************************
-- Get the next database name
FETCH NEXT
FROM csrDB
INTO @DBName
-- ***************************************************************************
-- End of the cursor loop
END
-- ***************************************************************************
-- Close and deallocate the CURSOR
CLOSE csrDB
DEALLOCATE csrDB
-- ***************************************************************************
-- Return the Database User data
SELECT *
FROM ##DBUsers
ORDER BY
--[Database Role], [Database User ID] -- Order by DB ROLE to spot dbowners
[server login]
--[Database Role], [Database] -- Order by DB ROLE to spot dbowners
--[Database], [Database Role] -- Order by DB ROLE to spot dbowners
--[Database], [Database User ID]
-- ***************************************************************************
GO
-- Clean up - delete the Global temp tables
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
DROP TABLE ##Users
GO
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
DROP TABLE ##DBUsers
-- ***************************************************************************
GO
October 28, 2009 at 9:15 am
krypto69. SA access is granted at the server/instance level and affects all databases.
In SSMS,:
* Connenct to the SQL
* Expand Security then Server Roles
* Open sysadmin
This gives you who have SA access to the server. Repeat for all the main servers.
The previous post will give you DB Owner permissions and may not be what you want.
Also, you can run this:
use master
go
exec sp_helpsrvrolemember 'sysadmin'
go
October 28, 2009 at 10:08 am
Awesome
THANKS GUYS
GOOD KARMA TO YOU ALL!!
October 28, 2009 at 10:14 am
Finding the people who may know the SA password is another issue that you will need to treat more like a persistent audit.
To track that you may try a for logon trigger and push key info to a Table in an Audit database (such things as Hostname, IpAddress, timestamp). Doing this will at the very least let you know who is logging into the database with the SA password. With the trigger you can have it simply search for sa, or audit all logons.
Just a thought.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 28, 2009 at 1:03 pm
Here's a tip I picked up some time ago that I always use regarding Domain Admin, Local Admin and SA.
Create domain groups, populate accordingly and grant equivalent rights to the (un-)holy trinity above. Go to goodpassword.com or such like and generate long and complex passwords for these accounts. Store these away in a fire safe or in some other secure storage but don't tell anyone what these passwords are!
You should now have a safe and secure system; well, in relative terms anyway. 😉
Hope this helps.
October 28, 2009 at 1:09 pm
In some ways we went a step further..
We created a second sa type account and then set the sa password to some value that was randomized, even we don't know what it is, other than it is REALLY long. The we look for attempted access' to sa since none of our apps use it.
CEWII
October 28, 2009 at 1:35 pm
Elliott W (10/28/2009)
In some ways we went a step further..We created a second sa type account and then set the sa password to some value that was randomized, even we don't know what it is, other than it is REALLY long. The we look for attempted access' to sa since none of our apps use it.
CEWII
I think a key here is that none of the apps use the SA password.
Is it a best practice and should be the only practice to not use the SA password? MOST DEFINITELY YES!! Unfortunately, that is not always the case and sometimes can be a lengthy political mess to change the SA password as well as change the apps to not use the SA account.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 28, 2009 at 2:02 pm
When no one knows it, no one can use it.. It kind of ends that debate. And our security people would flat out decline anyone who wanted to, it is not open for debate.
CEWII
October 28, 2009 at 2:06 pm
No debate from me - I have inherited too many systems where sa was the only password used in the apps. Many times I thought about just changing the password and asking forgiveness after the fact. The real problem lies in getting developers to change the code to use a different account and adhere to best practices.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 28, 2009 at 2:19 pm
In smaller shops I can see that as a bigger challenge. In bigger shops, there are usually other groups who have a hand in setting policy and since sa use is an easy target that is usually covered. In small shops the DBA is usually the bad guy. Also in smaller shops you get a lot of "accidental" DBA's, who may not know any better until later..
I wish SSC had been around when I first was doing this work, it would have been a great asset to me as a beginner. Even more so than now.
CEWII
October 29, 2009 at 12:36 pm
Interesting responses..
Thanks guys!
October 30, 2009 at 9:12 am
Problem is we have a lot of purchased apps that REQUIRE to install you use the sa login to install it. In that install process it will create the valid userids and rights the app uses then. Odd I know but it is reality.
October 31, 2009 at 10:08 am
Markus (10/30/2009)
Problem is we have a lot of purchased apps that REQUIRE to install you use the sa login to install it. In that install process it will create the valid userids and rights the app uses then. Odd I know but it is reality.
In that case we would set the sa password and then change it later. We would also advise the vendor that that is a really questionable practice. But if it required sa to run, that would be an automatic disqualifier, it also signifies poorly written non-professional software, to me at least. and I know that as a rookie SQL developer in the 90's I did stupid stuff like that.. But I didn't have great resources like SSC to guide me..
But needing a highly priviliged user like a SysAdmin to install is understandable and acceptable. If required to run then it would probably get its own instance.
CEWII
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply