sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | List server, db, db user, db role


DECLARE @DBNAME NVARCHAR(MAX)
declare @nur table ([servername] nvarchar(max), [dbname] nvarchar(max), [user] nvarchar(max), [role] nvarchar(max));

DECLARE @SQL NVARCHAR(MAX)

DECLARE __DB_CUR__ CURSOR FOR
SELECT D.NAME FROM SYS.DATABASES D

OPEN __DB_CUR__

FETCH NEXT FROM __DB_CUR__ INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SQL =
 (';with dbr as (select * from [' + @DBNAME + '].sys.database_principals where type = ''R'')
 ,dbu as (select * from [' + @DBNAME + '].sys.database_principals where type IN (''S'', ''U'', ''G''))
 SELECT @@servername servername, ''' + @DBNAME + ''' db, u.name [user], r.name [role] FROM DBU u
  left join [' + @DBNAME + '].sys.database_role_members rl
   on u.principal_id = rl.member_principal_id
  left join dbr r on
   rl.role_principal_id = r.principal_id
 order by [user], [role];')
 INSERT INTO @nur
 EXEC (@SQL)

 FETCH NEXT FROM __DB_CUR__ INTO @DBNAME
END

CLOSE __DB_CUR__
DEALLOCATE __DB_CUR__


SELECT * FROM @nur
--==============================create user / add to role===================================
USE [master]
GO
CREATE USER [domain\user] FOR LOGIN [domain\user]
GO
USE [master]
GO
ALTER ROLE [db_owner] ADD MEMBER [domain\user]
GO


-- new version With SID

DECLARE @IF_DETAIL bit = 1
DECLARE @IF_NUR BIT = 1
DECLARE @IF_login_db_user_role BIT = 1
DECLARE @IF_NUR2 BIT = 1
DECLARE @IF_COUNT BIT = 1

DECLARE @COUNT_VALUE INT

SET NOCOUNT ON

DECLARE @DBNAME NVARCHAR(MAX)
declare @nur table (
  [servername] nvarchar(max)
, [dbname] nvarchar(max)
, [user] nvarchar(max)
, [role] nvarchar(max)
, SID_USER VARBINARY(85)
, SID_ROLE VARBINARY(85)
);

DECLARE @SQL NVARCHAR(MAX)

print '--BGN--<<<<<<========= @nur insert =========>>>>>>--'

DECLARE __DB_CUR__ CURSOR FOR
SELECT D.NAME FROM SYS.DATABASES D 
where convert(varchar(20),databasepropertyex(D.name, 'Status')) <> 'OFFLINE'

OPEN __DB_CUR__

FETCH NEXT FROM __DB_CUR__ INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN

if(@IF_DETAIL = 1)
begin
print  CHAR(10) + '--<<<<<<========= @nur insert : [' + @DBNAME + '] =========>>>>>>--' + CHAR(10)
end

SET @SQL = 
('; with 
 dbr as (select * from [' + @DBNAME + '].sys.database_principals where type = ''R'')
,dbu as (select * from [' + @DBNAME + '].sys.database_principals where type <> ''R'')
SELECT @@servername servername, ''' + @DBNAME + ''' db, u.name [user], r.name [role], U.SID, R.SID
FROM DBU u
left join [' + @DBNAME + '].sys.database_role_members rl
on u.principal_id = rl.member_principal_id
left join dbr r on
rl.role_principal_id = r.principal_id
order by [user], [role];')

INSERT INTO @nur
EXEC (@SQL)

if(@IF_DETAIL = 1)
begin
print @SQL
end

FETCH NEXT FROM __DB_CUR__ INTO @DBNAME
END

CLOSE __DB_CUR__
DEALLOCATE __DB_CUR__

IF (@IF_NUR = 1)
BEGIN
SELECT * FROM @nur
END

IF (@IF_COUNT = 1)
BEGIN
SELECT @COUNT_VALUE = COUNT(*) FROM @nur
print CHAR(10) + '--CNT--<<<<<<========= @nur insert =========>>>>>>--' + CHAR(10)
PRINT '::COUNT OF @nur : ' + CAST(@COUNT_VALUE AS NVARCHAR(MAX))
END

print CHAR(10) + '--END--<<<<<<========= @nur insert =========>>>>>>--' + CHAR(10) + CHAR(10)

declare @login_db_user_role as table(
  login nvarchar(max)
, dbname nvarchar(max)
, [user] nvarchar(max)
, [role] nvarchar(max)
)

print '--BGN--<<<<<<========= @login_db_user_role insert =========>>>>>>--'

;with login_db_user_role as(
SELECT distinct sp.name [login], n.dbname, n.[user], n.[role]  
FROM @nur n right join sys.server_principals sp
on n.SID_USER = sp.sid
where sp.type <> 'R'
--add the login name filters here
--where sp.name .....
) 
insert into @login_db_user_role
select * from login_db_user_role l

IF (@IF_login_db_user_role = 1)
BEGIN
select * from @login_db_user_role
END

IF (@IF_COUNT = 1)
BEGIN
SELECT @COUNT_VALUE = COUNT(*) FROM @login_db_user_role
print CHAR(10) + '--CNT--<<<<<<========= @login_db_user_role insert =========>>>>>>--' + CHAR(10)
PRINT ('::COUNT OF @login_db_user_role : ' + CAST(@COUNT_VALUE AS NVARCHAR(MAX)))
END

print CHAR(10) + '--END--<<<<<<========= @login_db_user_role insert =========>>>>>>--' + CHAR(10) + CHAR(10)


DECLARE @DBNAME2 NVARCHAR(MAX)
declare @nur2 table (
 [servername] nvarchar(max), 
 [dbname] nvarchar(max), 
 GRANTEE_ID BIGINT,
 GRANTEE_NAME NVARCHAR(MAX),
 GRANTEE_TYPE NVARCHAR(MAX),
 GRANTOR_ID BIGINT,
 GRANTOR_NAME NVARCHAR(MAX),
 OBJECT_ID BIGINT,
 OBJECT_NAME NVARCHAR(MAX),
 OBJECT_TYPE NVARCHAR(MAX),
 permission_name NVARCHAR(MAX),
 state_desc NVARCHAR(MAX)
);

DECLARE @SQL2 NVARCHAR(MAX)

print '--BGN--<<<<<<========= @nur2 insert =========>>>>>>--'

DECLARE __DB_CUR2__ CURSOR FOR
SELECT D.NAME FROM SYS.DATABASES D

OPEN __DB_CUR2__

FETCH NEXT FROM __DB_CUR2__ INTO @DBNAME2
WHILE @@FETCH_STATUS = 0
BEGIN

if(@IF_DETAIL = 1)
begin
print  CHAR(10) + '--<<<<<<========= @nur2 insert : [' + @DBNAME + '] =========>>>>>>--' + CHAR(10)
end

SET @SQL2 =
 'SELECT 
 @@SERVERNAME SERVERNAME
 , ''' + @DBNAME2 + ''' DBNAME
 , P.grantee_principal_id GRANTEE_ID
 , PC.name GRANTEE_NAME
 , PC.TYPE_DESC GRANTEE_TYPE
 , P.grantor_principal_id GRANTOR_ID
 , PCR.name GRANTOR_NAME
 , P.major_id OBJECT_ID
 , O.name OBJECT_NAME 
 , O.type_desc OBJECT_TYPE
 , P.permission_name
 , P.state_desc
 FROM [' + @DBNAME2 + '].SYS.database_permissions P
 LEFT JOIN [' + @DBNAME2 + '].SYS.objects O
 ON P.major_id = O.object_id
 INNER JOIN [' + @DBNAME2 + '].SYS.database_principals PC
 ON P.grantee_principal_id = PC.principal_id
 INNER JOIN [' + @DBNAME2 + '].SYS.database_principals PCR
 ON P.grantor_principal_id = PCR.principal_id
 ORDER BY GRANTEE_ID, OBJECT_NAME'

if(@IF_DETAIL = 1)
begin
print @SQL2
end

INSERT INTO @nur2
EXEC (@SQL2)

FETCH NEXT FROM __DB_CUR2__ INTO @DBNAME2
END

CLOSE __DB_CUR2__
DEALLOCATE __DB_CUR2__

IF (@IF_NUR2 = 1)
BEGIN
SELECT * FROM @nur2
--where permission_name = 'connect'
END


IF (@IF_COUNT = 1)
BEGIN
SELECT @COUNT_VALUE = COUNT(*) FROM @nur2
print CHAR(10) + '--CNT--<<<<<<========= @nur2 =========>>>>>>--' + CHAR(10)
PRINT ('::COUNT OF @nur2 : ' + CAST(@COUNT_VALUE AS NVARCHAR(MAX)))
END

print CHAR(10) + '--END--<<<<<<========= @nur2 =========>>>>>>--' + CHAR(10) + CHAR(10)


;WITH LOGIN_USER_PERMISSION AS (
SELECT L.*, N.OBJECT_NAME, N.OBJECT_TYPE, N.PERMISSION_NAME, N.STATE_DESC
FROM @NUR2 N right join @login_db_user_role L
ON N.dbname = ISNULL(L.dbname, '') and N.GRANTEE_NAME = ISNULL(L.[user], '')
where L.dbname is not null
union
SELECT L.*, N.OBJECT_NAME, N.OBJECT_TYPE, N.PERMISSION_NAME, N.STATE_DESC
FROM @NUR2 N right join @login_db_user_role L
ON N.dbname = ISNULL(L.dbname, '') and N.GRANTEE_NAME = ISNULL(L.[role], '') 
and GRANTEE_TYPE = 'DATABASE_ROLE'
where N.dbname is not null AND L.[role] is not null)
SELECT * FROM LOGIN_USER_PERMISSION
--where login = 'COLOGNE\dmechmann' --/*
--WHERE [login] like 'DC20GTDEVDB03%' --(host_name() + '%')
--*/
--return
select distinct 
'use master' + char(10)
+ 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + l.login + ''')' + char(10)
+ 'CREATE LOGIN [' + l.login + '] ' + CASE WHEN l.login LIKE '%\%' THEN 'FROM WINDOWS' ELSE ' WITH PASSWORD=N''password''' END + char(10)
+ 'GO'  + char(10) from @login_db_user_role l

select 
'USE [' + l.dbname + ']' + char(10)
+ 'GO' + char(10)
+ 'IF NOT EXISTS (SELECT * FROM ' + l.dbname + '.sys.database_principals WHERE name = N''' + l.[user] + ''')' + char(10)
+ 'CREATE USER [' + l.[user] + '] FOR LOGIN [' + l.login + ']' + char(10)
+ 'GO' + char(10)
+ 'USE [' + l.dbname + ']' + char(10)
+ 'GO' + char(10)
+ 'if @@VERSION like ''Microsoft SQL Server 2012%''' + char(10) --select @@VERSION
+ ' begin' + char(10)
+ ' DECLARE @SSQQLL NVARCHAR(MAX) ' + char(10)
+ ' SET @SSQQLL = ''USE [' + l.dbname + '] '' + CHAR(10) + '''
+ 'ALTER ROLE [' + l.role + '] ADD MEMBER [' + l.[user] + ']''' + char(10)
+ ' EXEC (@SSQQLL)' + char(10)
+ ' end' + char(10)
+ 'else' + char(10)
+ ' begin' + char(10)
+ ' EXEC sp_addrolemember N''' + l.role + ''', N''' + l.[user] + '''' + char(10)
+ ' end' + char(10)
+ 'GO' + char(10)
from @login_db_user_role l inner join master.sys.databases db on
l.dbname = db.name and convert(varchar(20),databasepropertyex(db.name, 'Status')) <> 'OFFLINE'





SELECT 
 sp.principal_id
 , SP.name login
 , SP2.principal_id
 , SP2.name ROLE
 
 , case when SP2.principal_id is not null then 
 'EXEC master..sp_addsrvrolemember @loginame = N''' + SP.name + 
 ''', @rolename = N''' + SP2.name + '''' + CHAR(10) + 'GO' + 
 CHAR(10) else '' end addLoginToRole
FROM 
SYS.server_principals SP 
 left join SYS.server_role_members SRM
 ON SRM.member_principal_id = SP.principal_id
 LEFT join SYS.server_principals SP2
 on SRM.role_principal_id = SP2.principal_id
where sp.type <> 'R'
order by case when sp2.principal_id is null then -1 else 1 end desc, sp2.principal_id


SELECT 
 sp.principal_id
 , SP.name login
 , SP2.principal_id
 , SP2.name ROLE
FROM 
SYS.server_principals SP 
 left join SYS.server_role_members SRM
 ON SRM.member_principal_id = SP.principal_id
 LEFT join SYS.server_principals SP2
 on SRM.role_principal_id = SP2.principal_id
where sp.type <> 'R'
order by case when sp2.principal_id is null then -1 else 1 end desc, sp2.principal_id




Comments