November 28, 2008 at 8:40 am
Hi,
I recently started a job with a large company that has hundreds of servers each with dozens of instances of sql server. Management would like me to audit each instance on every server for users and permissions. I sure hope there is a script that will do this for me! They would also like this compared with the Active Directory and the report written to a Excel spread sheet. Does anyone know of a script that will do all of this for me?
Thanks.
November 28, 2008 at 9:25 am
Hi,
When you say "audit" I am thinking something that goes on continuously but you are looking for a one time inventory, or?
Can you give a little bit more details about what you need to know about you users and their permissions - are they asking for a list of all users AND all their permissions on all objects in your database(s)? Or a list of users and which databases they have access to?
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 28, 2008 at 9:47 am
Following two scripts may help you. First one gives you a breakdown of permissions on the server level and secon one shows them on the object level. Scripts are a bit "raw" but it should be quite easy to tailor them.
select
a.[User]
,a.[Class Desciption] [Applies To]
,b.[Covering Permission Name]
,a.[Effective permission]
,a.[Permission]
from
(
select
sysPrinc.[name][User]
,sysPerm.[class_desc][Class Desciption]
,sysPerm.[Type][Type]
,sysPerm.[permission_name][Effective permission]
,sysPerm.[state_desc][Permission]
from
sys.server_permissionssysPerm
inner join sys.server_principalssysPrinc
on sysPerm.grantee_principal_id=sysPrinc.principal_id
)a
left outer join
(
SELECT
[type]
,[covering_permission_name] [Covering Permission Name]
FROM fn_builtin_permissions(default)
where class_desc ='server'
)b
on a.type=b.type
order by a.
select a.[UserName]
,a.[class_desc]
,b.[sname] [Schema]
,b.[name] [Object]
,a.[minor_id]
,a.[Type]
,a.[permission_name]
,a.[state_desc]
from
(
select
sysPrinc.[name][UserName]
,sysPerm.[class_desc]
,sysPerm.[major_id]
,sysPerm.[minor_id]
,sysPerm.[Type]
,sysPerm.[permission_name]
,sysPerm.[state_desc]
from
sys.database_permissionssysPerm
inner join sys.database_principalssysPrinc
on sysPerm.grantee_principal_id=sysPrinc.principal_id
)a
left join
(
select sSch.[name] [sname],sSysObj.[name],sSysObj.[id] from sys.sysobjects sSysObj inner join sys.schemas sSch on sSysObj.[uid]=sSch.[schema_id]
union
select sSch.[name] [sname],sSysObj.[name],sSysObj.[id] from master.sys.sysobjects sSysObj inner join sys.schemas sSch on sSysObj.[uid]=sSch.[schema_id]
)b
on a.[major_id]=b.[id]
--where a.[UserName]='sa'
December 1, 2008 at 7:30 am
Elisabeth,
Your exactly right. This will be both a one-shot and possibly a continuous audit of our database systems.
This is what they are looking for.
RDBMS server name
Database name
User name
Permissions and roles
There are 150,000 employees in this company and each of them use a
database. We have lost track of who is doing what and their rights, etc.
I need a script that runs and first identify the servers and then the instances running on it and the the users and permissions. If I have to do this one server/instance at a time I will be retirement age before its done.
Thanks.
December 1, 2008 at 11:13 pm
Hi,
You can use a tool such as SQLPing to discover the SQL Server instances (slqcmd or OSQL is not going to find servers that are listening to fixed port and where the SQL Browser is not running).
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
You probably also want to run something like
SELECT * FROM sys.configurations
... to document which options are set on the servers.
To iterate through your databases you can use the sp_MSForeeachdb stored procedure (undocumented in Books Online but documented on the web).
I have attached a script that lists permissions and role membership. Have a look at that and the script that has already been posted in the thread. You probably want to change the level of detail.
Good luck!
/elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
December 2, 2008 at 7:45 am
Hi,
My company is doing a similar audit where we are determining how many valid installs of SQL we have running on the different servers. The tool we are using can pull registry keys, check add and remove programs ext... My problem is I am having a hard time differentiating between a full install of SQL 2005 and a server\desktop that only has Management Studio installed. Is there an easy way to differentiate between the two like a registry value I can pull back?
Thanks for any help you can give me,
Frank
December 3, 2008 at 7:45 am
Hi,
Thanks to everyone for all your help. I really appreciate it.
Frank, I am having the same problem and in addition management today
added all of the sql 2k instances and Oracle. I know nothing about how to discover users and permissions on Oracle. I do appreciate the experience they are giving me though.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply