September 30, 2011 at 11:00 am
Hi,
I need to identify what logins exist and what access they have to each database on the server.
I have the code below which gives me everything except the database name.
Does anyone have any suggestions as to how to get the DB name (sys.databases?) into the result set of this query?
SELECT
Principal.name AS [UserName]
, Principal.type_desc AS [UserType]
, dbRole.Name AS [DatabaseRole]
FROM
sys.database_principals Principal
--Identify the members linked to DB roles:
INNER JOIN sys.database_role_members RoleMember
ON Principal.principal_id = RoleMember.member_principal_id
--Get the name of role: Roles are in fact principals so we join back to the principal table to get role name.
INNER JOIN sys.database_principals DbRole
ON RoleMember.role_principal_id = DbRole.principal_id
--Link to server principles
INNER JOIN sys.server_principals ServerPrincipal
ON Principal.SID = ServerPrincipal.SID
All suggestions are welcome.
G
September 30, 2011 at 12:23 pm
How about this?
SELECT
DB_NAME() as DatabaseName,
Principal.name AS [UserName]
, Principal.type_desc AS [UserType]
, dbRole.Name AS [DatabaseRole]
FROM
sys.database_principals Principal
--Identify the members linked to DB roles:
INNER JOIN sys.database_role_members RoleMember
ON Principal.principal_id = RoleMember.member_principal_id
--Get the name of role: Roles are in fact principals so we join back to the principal table to get role name.
INNER JOIN sys.database_principals DbRole
ON RoleMember.role_principal_id = DbRole.principal_id
--Link to server principles
INNER JOIN sys.server_principals ServerPrincipal
ON Principal.SID = ServerPrincipal.SID
September 30, 2011 at 1:29 pm
My original post appears to be ambiguous.
There are multiple databases on the server and I need the login and role for each database.
I couldn't find a link between sys.server_principals and sys.databases to accomplish this.
Apologies for the unclear post Lynn.
G
September 30, 2011 at 1:56 pm
This isn't what you are looking for exactly but it is something that could point you in the right direction. Run it, modify it, what ever then report back what you get from it.
create table #dbusers (
DBName sysname not null,
UserName sysname not null,
PrincipalId int not null,
PrincipalType char(1) not null,
USid varbinary(85) null,
DefaultSchemaName sysname null
);
set nocount on;
declare @SQLCmd varchar(max);
select @SQLCmd = ISNULL(@SQLCmd, 'set nocount on;' + CHAR(13) + CHAR(10)) +
'insert into #dbusers (DBName, UserName, PrincipalId, PrincipalType, USid, DefaultSchemaName) ' +
'select ''' + db.name + ''', dp.name, dp.principal_id, dp.type, dp.sid, dp.default_schema_name from [' +
'' + db.name + '].sys.database_principals dp ' +
'left outer join [' + db.name + '].sys.schemas sc on (dp.principal_id = sc.principal_id) ' +
'where dp.type in (''S'',''G'',''U'') and dp.name not in (''INFORMATION_SCHEMA'',''dbo'',''guest'',''sys'') and sc.principal_id is null;' + CHAR(13) + CHAR(10)
from
sys.databases db
where
db.database_id > 4
order by
db.database_id
;
set @SQLCmd = @SQLCmd + CHAR(13) + CHAR(10) + 'SET NOCOUNT OFF;';
set nocount off;
-- print @SQLCmd;
exec (@SQLCmd);
select
*
from
#dbusers dbu
left outer join sys.server_principals sp
on (dbu.USid = sp.sid)
where
1 = 1
--not exists(Select 1 from sys.server_principals sp where sp.name = dbu.UserName)
--and
--not exists(Select 1 from sys.server_principals sp where sp.sid = dbu.USid)
-- and sp.sid is null
;
drop table #dbusers;
October 25, 2011 at 9:41 am
Hello,
Apologies to necro the thread but I got distracted before I could respond to the thread.
Thank you for the suggestions Lynn. However I just managed to confuse myself building a sql string to get my result; too many video games as child i guess 😀
Thankfully someone built sp_MSForEachDB. It does appear to be unsupported by Microsoft but hopefully they don't get rid of it before I change careers 😉
The following sql gave me what I needed; I hope it helps someone out there in a similar situations.
CREATE TABLE #DbUserList(
DatabaseName VARCHAR(100)
, UserName VARCHAR(100)
, UserType VARCHAR(100)
, DatabaseRole VARCHAR(100)
)
INSERT INTO #DbUserlist
EXEC sp_MSForEachDB
'SELECT
"?" AS DatabaseName
, Principal.name AS [UserName]
, Principal.type_desc AS [UserType]
, dbRole.Name AS [DatabaseRole]
FROM
[?].sys.database_principals Principal
INNER JOIN [?].sys.database_role_members RoleMember
ON Principal.principal_id = RoleMember.member_principal_id
INNER JOIN [?].sys.database_principals DbRole
ON RoleMember.role_principal_id = DbRole.principal_id
INNER JOIN [?].sys.server_principals ServerPrincipal
ON Principal.SID = ServerPrincipal.SID'
SELECT * FROM #DbUserlist
DROP TABLE #DbUserlist
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply