January 31, 2012 at 9:20 am
Hello Everyone,
I am having a little trouble with a select statement, I am trying to make a list of all the users of a database, access profiles, their creation and modification datesfor several user databases.
When I run select* from sys.logins, I get alot of what I need, but I am having trouble, as when I run this..
Select a.name, a.type,a.create_date, a.modify_date, b.default_database_name, b.type_Desc
from sys.database_principals as a
inner join sys.sql_logins as b
on a.principal_id = b.principal_id
where b.type = 'S'
and b.default_database_name='<default_DB>'
I only get one row back were the user is 'dbo'. What am I doing wrong?
Thanks for any help,
Regards, D.
January 31, 2012 at 9:44 am
sys.sql_logins contains only SQL Server logins, not the Windows-authenticated logins.
Do you need info form that view? Dates of creation and modification are in sys.database_principals.
January 31, 2012 at 9:47 am
Hello, yes I need both windows and sql logins, I have found the sys.database_principles.
Regards,
D.
January 31, 2012 at 10:00 am
I think your confusion is the assumption that server login = database principal (as noted by your join statement).
Logins are indeed principals but not mapped to database user principals in that manner (by principal_id).
January 31, 2012 at 10:02 am
Well not really, I was just using it as a way to join the tables.
Regards,
D.
January 31, 2012 at 10:15 am
What precisely do you mean by "access profiles" in the original question? Server roles?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2012 at 10:18 am
HI GSquared, sorry yes, the database roles, not the server roles.
Regards,
D.
January 31, 2012 at 10:30 am
Duran (1/31/2012)
Well not really, I was just using it as a way to join the tables.
The point is it doesn't join the tables. That's like joining employee to department on EmployeeID = DepartmentID. Might occasionally produce a result, but not the ones you want.
This should get you started, it'll show the database principals (users) in the current DB and their matching logins. It will only show users that are in that DB, not logins that have implicit permissions (say from sysadmin server role)
SELECT *
FROM sys.server_principals AS sp
INNER JOIN sys.database_principals AS dp ON sp.sid = dp.sid
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2012 at 10:33 am
Here's a list of the views for security data in SQL Server: http://msdn.microsoft.com/en-us/library/ms178542.aspx
The ones you probably want are sys.server_principles, sys.database_principles, and sys.database_role_members.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2012 at 1:44 am
Hey there,
Thank you Gail and GSquared, I try everything you have given me, hopefully I should be ok. Much appreciated.
Kind Regards,
D.
February 1, 2012 at 6:45 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply