April 14, 2012 at 5:21 am
HI Team,
i want all logins and groups in a instance in sql server 2008 R2
can you please provide script for this
i can use sp_help logins, sys.syslogins, it is correct for to get all logins or not.
for groups what should i do.
tx
April 16, 2012 at 8:42 am
shiv-356842 (4/14/2012)
HI Team,i want all logins and groups in a instance in sql server 2008 R2
can you please provide script for this
i can use sp_help logins, sys.syslogins, it is correct for to get all logins or not.
for groups what should i do.
tx
Do not use syslogins anymore. That is a System View left in place for backwards compatibility. Use sys.server_principals going forward: sys.server_principals
Have a look at the type_desc column in the documentation. By issuing a SELECT statement with the proper filter on that column you can see all SQL Logins, Windows Logins and Windows Groups as needed.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 16, 2012 at 4:56 pm
As said, syslogins is deprecated. It is included only for backward compatibility and shouldn't be used if you're not querying SQL Server 2000 systems. The difference betwen sys.server_principals and sys.sql_logins is that sys.sql_logins only covers SQL Server based logins (where SQL Server stores the password) and has more columns related to keeping track of the password. As indicated, you want to use sys.server_principals at the server levels. This is from SQL Server 2005 and above.
K. Brian Kelley
@kbriankelley
April 17, 2012 at 8:35 am
If it helps, here is the URL to a script I wrote to do a "Instance Security Audit Documentation" script. It will print in normal report format, or you can change a variable to make it actually do the scripts to set the permissions. Shows permissions for each database, roles, and users. Also shows access at the server level (first thing in the report), showing who is assigned to each fixed-server role.
http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx#bm1230153
It's not fast or pretty, but it works for what I need it to do.
May 10, 2012 at 7:40 pm
vikingDBA (4/17/2012)
If it helps, here is the URL to a script I wrote to do a "Instance Security Audit Documentation" script. It will print in normal report format, or you can change a variable to make it actually do the scripts to set the permissions. Shows permissions for each database, roles, and users. Also shows access at the server level (first thing in the report), showing who is assigned to each fixed-server role.http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx#bm1230153
It's not fast or pretty, but it works for what I need it to do.
Such a very amazing link!
__________________
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply