(imported topic written by JS6591)
Hi,
Does anyone know how to retrieve MSSQL Server user accounts and permissions information?
(imported comment written by MrFixit)
I utilize sqlcmd.exe in a task which is part one of a two part audit. The task will run a sql query and sticks the information into a file and run the task at some frequency, I run daily. You can also do the same in a vbscript if you are looking to do more processing of the results before sitcking it in a file. For infomation that is considered somewhat sensitive, you can put the results in a file that only SYSTEM account has access to. Try running the SQLCMD command on a system so you can see what it is doing. You can google for an example query that will pull users / permission information. Also note that you are running this in the context of the SYSTEM account which typically has visibility into most installations of SQLServer.
The second part is an analysis that pulls the values from the text file I create with the part one to grab the data or use it for targeting corrective actions in Fixlets.
Here is an example what could be in the task:
//Establish a ServerName value. This must include the hostname and instance or clustername and instance if applicable.
parameter “ServerName” = “{this is the tricky part}”
waithidden cmd.exe /C sqlcmd.exe -S {parameter “ServerName” of action} -E -h-1 -W -s “|” -Q “SELECT name + ’ UserAccess’, user_access_desc as ‘info’ FROM sys.databases WHERE source_database_id IS NULL union SELECT name + ’ RecoveryModel’, recovery_model_desc as ‘info’ FROM sys.databases WHERE source_database_id IS NULL union SELECT name + ’ Broker’, cast(is_broker_enabled as varchar) as’info’ FROM sys.databases WHERE source_database_id IS NULL union SELECT name + ’ Owner’, suser_sname(owner_sid) as ‘info’ FROM sys.databases WHERE source_database_id IS NULL union select a.name + ’ ’ + b.name + ’ FileName’, b.filename COLLATE SQL_Latin1_General_CP1_CI_AS as ‘info’ from sys.sysdatabases a inner join sys.sysaltfiles b on a.dbid = b.dbid union select a.name + ’ ’ + b.name + ’ MaxSize’, cast(b.maxsize as varchar) as ‘info’ from sys.sysdatabases a inner join sys.sysaltfiles b on a.dbid = b.dbid union select a.name + ’ ’ + b.name + ’ Growth’, cast(b.growth as varchar) as ‘info’ from sys.sysdatabases a inner join sys.sysaltfiles b on a.dbid = b.dbid” -o “{pathname of parent folder of regapp “BESClient.exe” & “\SQLInfo.txt”}”