asked
How to list login, the database it can use and fixed roles
I want to have a query to system views, which returns login, dbname,fixed_role Where dbname is the database the login is a user. For each login, there may be 0,1, or more dbname. And for each login, dbname, there may be 0,1, or more fixed_role. What system views I need to get this info? Thanks
Don't forget to vote up all helpful answers by clicking on the thumbs up next to them and mark any final answer by clicking on the check mark next to it.
If you want to view database users you want to use the `sys.database_principals` view. This is specific to the database you are connected to. If you want to view server logins then use the `sys.server_principals` view in the MSDB database.
Great question. If you run USE msdb GO SELECT * FROM sys.server_principals GO USE master GO SELECT * FROM sys.server_principals and look at the execution plans or the definitions of the views then they both look exactly the same.
@FatherJack - Just how different is the sys.server_principals in msdb database and master database? Why was there the need to place the view (and other such similar [sys] schema views) in two different system databases?
Yes indeed! So, what do we infer from the above observation? There must have been some reason that the views were duplicated in master as well as msdb. Honestly, I was of the assumption that sys-schema views were only present in master database, however, it was when you referred msdb in your answer to luggg's question above that I too came to know of it.
Finally got the answers to this thread - Do Refer
http://ask.sqlservercentral.com/questions/107025/difference-between-sys-schema-views-in-master-and.html
I partly got the answer to list logins and all database in which it is a user: create table #loginuser (login sysname, defaultdb varchar(50),isuserof varchar(50),issyadmin int); DECLARE @command varchar(1000) SELECT @command = 'USE [?] select l.login,l.defaultdb,d.db isuserof,sysadmin from ( select name login,dbname defaultdb,hasaccess,isntname,sysadmin,sid loginsid from syslogins where substring(name,1,2) not in (''##'',''NT'') ) l left join ( select db_name() db,uid,name username,hasdbaccess,islogin,issqlrole,sid usersid from sysusers where uid <10000 ) d on l.loginsid=d.usersid' --EXEC sp_MSforeachdb @command; insert into #loginuser EXEC sp_MSforeachdb @command ; select @@servername,* from #loginuser where isuserof is not null order by isuserof ; The result is like: Server_name login default_db isuserof_db sysadmin WIN-EVHR6GD8156 sa master C2CSearch 1 WIN-EVHR6GD8156 sa master housekeep 1 WIN-EVHR6GD8156 glu master guvnor 1 WIN-EVHR6GD8156 glu master lportal 1 The next thing I want to know is what fixed roles the login (now also database user) has in the database in which it is a user (isuserof_db). What system views I can use to get the roles granted to the database user
17 People are following this question.
