April 18, 2007 at 12:56 pm
Hello,
I need to generate a simple listing of database users for audit purposes. In sql server 2000, this was an easy task. All one had to do was choose the appropriate database, then right click on users, then choose the export list option. Simple user reports generated and formatted quickly and easily. I can not seem to locate this functionality in Sql Server 2005 ?
Has this functionality disappeared or is it hidden somewhere in the menus or do I need to install some special component in order to get this feature of 2000 back ?
Please share your expertise regarding how to generate simple database user listings in Sql Server 2005.
Thanks in advance.
Don
April 18, 2007 at 1:08 pm
We can get the list of users from sys.database_principals. If you want to list only SQL_USER then you can specify type_desc='SQL_USER' in the where clause. If you do not specify any condition in your where clause, you will get all user types.
April 18, 2007 at 1:14 pm
Hello,
Thank you for the response. Can you point me to where the sys.database_principals table exists ? I can not seem to located it in the master, msdb, or any other databases ?
April 18, 2007 at 1:19 pm
It exists in each of the databases for which you would like the list. For Eg: If you want the list of users in master database, select Master and give the statement select * from sys.database_principals
The list of users will be displayed for Master database. You can do the same for that database you want the list for.
April 18, 2007 at 1:52 pm
ksharma,
Hello, I followed your advice and came up with the following :
select * from sys.database_principals where type_desc in ('SQL_USER', 'WINDOWS_USER')
This produced the results I was looking for.
I thank you for sharing your expertise.
Good Day !!!
Don
April 18, 2007 at 1:56 pm
Thank you very much for trying the solution. Very happy that I could be of some help.
Good Day to you too!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply