Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2014
    Posts
    7

    Unanswered: List all databases that a user has access to

    Hello all,

    Can you please suggest a way to list all databases that a user has access to?

    Thanks.
    Cindy

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by newCindy View Post
    Hello all,

    Can you please suggest a way to list all databases that a user has access to?

    Thanks.
    Cindy
    SELECT name
    FROM sys.sysdatabases
    WHERE HAS_DBACCESS(name) = 1
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Aug 2014
    Posts
    7
    thank you very much for getting back to me. But, do I need to run this query for each of the database? I have an user account for which I want to see all the databases that it has access.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try this:
    Code:
    set nocount on
    
    --SQLServerSecurityGrid.sql
    --Bruce Lindman (sqlblindman@gmail.com), 2013
    --Shows the highest server and database security level for all server user accounts
    
    --Execution parameters
    declare	@IncludeOrphans char(1) = 'Y'
    declare	@MaxPermissionLevel int = null
    	--null = All databases and logins
    	--db_owner = 0 --Can perform all configuration and maintenance activies.
    	--db_accessadmin = 1 --Can add or remove access for logins.
    	--db_securityadmin = 2 --Can modify role membership and manage permissions.
    	--db_ddladmin = 3 --Can run any DDL command.
    	--db_datawriter = 4
    	--db_datareader = 5
    	--db_backupoperator = 6
    	--db_denydatawriter = 7
    	--db_denydatareader = 8
    
    declare	@SQLString varchar(max)
    declare	@PivotColumns varchar(max)
    
    create table #LoginRights
    		(DisplayOrder int,
    		LoginName varchar(128),
    		Scope varchar(128),
    		PermissionLevel int,
    		Setting varchar(128))
    
    insert into #LoginRights
    		(DisplayOrder,
    		LoginName,
    		Scope,
    		PermissionLevel,
    		Setting)
    select	1 as DisplayOrder,
    		Logins.name as LoginName,
    		Roles.name as Scope,
    		0 as PermissionLevel,
    		'Granted' as Setting
    from	master.sys.server_principals as Logins
    		left outer join master.sys.server_role_members on Logins.principal_id = server_role_members.member_principal_id
    		left outer join master.sys.server_principals as Roles on server_role_members.role_principal_id = Roles.principal_id
    
    select	@SQLString = coalesce(@SQLString, '') + '
    use [' + name + ']
    
    ;with	Exceptions as
    			(select	''msdb'' as DatabaseName, ''MS_DataCollectorInternalUser'' as LoginName
    			union select ''msdb'' as DatabaseName, ''##MS_PolicyEventProcessingLogin##'' as LoginName
    			union select ''msdb'' as DatabaseName, ''##MS_PolicyTsqlExecutionLogin##'' as LoginName
    			union select null as DatabaseName, ''dbo'' as LoginName),
    		PermissionRanks as
    			(select	2 as DisplayOrder,
    					coalesce(suser_sname(Principals.sid), Principals.name) as LoginName,
    					''' + name + ''' as Scope,
    					case when suser_sname(Principals.sid) is null then ''Orphaned'' else convert(char(50), Roles.name) end as Setting,
    
    							case Roles.name --Just return the role with the highest level permissions.
    								when ''db_owner'' then 0 --Can perform all configuration and maintenance activies.
    								when ''db_accessadmin'' then 1 --Can add or remove access for logins.
    								when ''db_securityadmin'' then 2 --Can modify role membership and manage permissions.
    								when ''db_ddladmin'' then 3 --Can run any DDL command.
    								when ''db_datawriter'' then 4
    								when ''db_datareader'' then 5
    								when ''db_backupoperator'' then 6
    								when ''db_denydatawriter'' then 7
    								when ''db_denydatareader'' then 8
    								else 9
    							end as PermissionLevel,
    
    					ROW_NUMBER() over
    						(Partition by Principals.sid
    						order by 
    							case Roles.name --Just return the role with the highest level permissions.
    								when ''db_owner'' then 0 --Can perform all configuration and maintenance activies.
    								when ''db_accessadmin'' then 1 --Can add or remove access for logins.
    								when ''db_securityadmin'' then 2 --Can modify role membership and manage permissions.
    								when ''db_ddladmin'' then 3 --Can run any DDL command.
    								when ''db_datawriter'' then 4
    								when ''db_datareader'' then 5
    								when ''db_backupoperator'' then 6
    								when ''db_denydatawriter'' then 7
    								when ''db_denydatareader'' then 8
    								else 9
    							end,
    							Roles.name) as PermissionRank
    				from	sys.database_principals as Principals
    						inner join sys.database_role_members on Principals.principal_id = database_role_members.member_principal_id
    						inner join sys.database_principals as Roles on database_role_members.role_principal_id = Roles.principal_id
    						left outer join Exceptions
    							on ''' + name + ''' = coalesce(Exceptions.DatabaseName, ''' + name + ''')
    							and coalesce(suser_sname(Principals.sid), Principals.name) = coalesce(Exceptions.LoginName, suser_sname(Principals.sid), Principals.name)
    				where	Principals.Type not in (''R'') --Exclude roles
    						and coalesce(Exceptions.DatabaseName, Exceptions.LoginName) is null
    				)
    insert into #LoginRights
    		(DisplayOrder,
    		LoginName,
    		Scope,
    		PermissionLevel,
    		Setting)
    select	DisplayOrder,
    		LoginName,
    		Scope,
    		PermissionLevel,
    		Setting
    from	PermissionRanks
    where	PermissionRank = 1
    '
    from	master.sys.databases
    where	databases.state = 0 --Only check Online databases
    
    exec (@SQLString)
    
    if @IncludeOrphans <> 'Y' delete from #LoginRights where Setting = 'Orphaned'
    if @MaxPermissionLevel is not null delete from #LoginRights where PermissionLevel > @MaxPermissionLevel or Scope is null
    
    ;with	PivotStrings as
    		(select	distinct
    				'
    		max(case when Scope = ''' + Scope + ''' then rtrim(Setting) else '''' end) as ''' + Scope + '''' as SQLString,
    				DENSE_RANK() over (order by DisplayOrder, Scope) as SortOrder
    		from	#LoginRights)
    select	@PivotColumns = coalesce(@PivotColumns + ', ', '') + SQLString
    from	PivotStrings
    order by SortOrder
    
    set		@SQLString = '
    select	convert(varchar(' + (select convert(varchar(3), MAX(len(LoginName))) from #LoginRights) + '), LoginName) as LoginName,' + @PivotColumns + '
    from	#LoginRights
    group by LoginName
    order by LoginName'
    
    exec (@SQLString)
    
    drop table #LoginRights
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2014
    Posts
    7
    Hi,

    I am getting an error: Invalid column name 'name'. What could be the cause? Can you help, please?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What version of SQL Server are you using?

  7. #7
    Join Date
    Aug 2014
    Posts
    7
    I am using SQL Server 2008 R2.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •