DECLARE @SQL NVARCHAR(MAX) DECLARE __DB_CUR__ CURSOR FOR OPEN __DB_CUR__ FETCH NEXT FROM __DB_CUR__ INTO @DBNAME FETCH NEXT FROM __DB_CUR__ INTO @DBNAME CLOSE __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 |
sql | SQL Server > sqlsrv | T-SQL >