listing the users and logins
-
I want to get a list of all users and logins for my SQL Server 2005 instance. I have, I think, 8 databases on it.
I have Googled and checked BOL, and have seen stuff about how to transfer logins from one to another, but I want to simply output a list of usernames and, if possible, what permissions they have. Is this doable/
-
http://www.mssqltips.com/tip.asp?tip=1818
This site gives excellant output on what you require.
Though i use the below mentioned script (dont remember the source). It was written by Norm Eager.
SELECT
name AS Login,
sysadmin =
CASE
WHEN sysadmin = 1 THEN 'X'
ELSE ''
END,
securityadmin =
CASE
WHEN securityadmin = 1 THEN 'X'
ELSE ''
END,
serveradmin =
CASE
WHEN serveradmin = 1 THEN 'X'
ELSE ''
END,
setupadmin =
CASE
WHEN setupadmin = 1 THEN 'X'
ELSE ''
END,
processadmin =
CASE
WHEN processadmin = 1 THEN 'X'
ELSE ''
END,
diskadmin =
CASE
WHEN diskadmin = 1 THEN 'X'
ELSE ''
END,
dbcreator =
CASE
WHEN dbcreator = 1 THEN 'X'
ELSE ''
END,
bulkadmin =
CASE
WHEN bulkadmin = 1 THEN 'X'
ELSE ''
END,
CONVERT(CHAR(16),createdate,120) AS 'DateCreated'
FROM master.dbo.syslogins
WHERE
sysadmin = 1
ORDER BY NAME
GO
Pradeep Singh -
This will not give you a simple list, but rather everything you wanted to know about users and their permissions. It produces a HTML document. The actual T-SQL is too long to post on this forum but search for:
[spAuditUsersPermissions]
/* Written By: Michelle Gutzait -- 8/18/2009
MSSQLTips.com
brought to you by Edgewood Solutions
I have used it and it works just great.
http://www.mssqltips.com/tip.asp?tip=1818
Edited 2:16 PM added link
-
that is a good start, but it only gives me a resultset of 10, when I can see many more listed when I expand the Logins tree.
this was in reference to the first reply.
-
Handy scripts!
-
bitbucket-25253 (10/21/2009)
that is a good start,
Are you commenting on the MSSQL Tips posting?
No, you posted that as I was writing my reply.
As for the one you posted, I ran it and it output a lot of info. Probably all I need. Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply