May 3, 2009 at 11:41 pm
Hi
In my server Group(Windows authentication) conatins around 20 users
How to view the users in sql server query form?,
and theire privilieges?
With regards
Lee
May 4, 2009 at 1:53 pm
This should give you a start.
Create Procedure SQL_ServerROLES as
Set nocount on
-- Author: Saleem Hakani
-- Date: Oct 15th 2007
-- Description: Below script will query SysLogins to find out which logins have what server role access and provides you with a beautiful grid of information.
-- Disclaimer: This script, is provided for informational purposes only and SQL Server Community (aka: http://WWW.SQLCOMMUNITY.COM) or the author
-- of this script makes no warranties,
-- either express or implied. This script, scenarios and other external web site references, is subject to change without notice.
-- The entire risk of the use or the results of the use of this script remains with the user.
Select
'Login Name'= Substring(upper(SUSER_SNAME(SID)),1,40),
'Login Create Date'=Convert(Varchar(24),CreateDate),
'System Admin' = Case SysAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Security Admin' = Case SecurityAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Server Admin' = Case ServerAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Setup Admin' = Case SetupAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Process Admin' = Case ProcessAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Disk Admin' = Case DiskAdmin
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End,
'Database Creator' = Case DBCreator
When 1 then 'YES (VERIFY)'
When 0 then 'NO'
End
from Master..SysLogins order by 3 Desc
exec SQL_ServerROLES
-- You can't be late until you show up.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply