Database Permissions extraction script

0

http://ramblingroseboutique.com/?prertwe=groupon-speed-dating-dc&1d8=b2 Dear DBAs,

Few days back one of my junior colleague reported me that while performing database refresh, he extracted the permissions of database before refresh and applied the permissions back on it after restore but still all permissions were not applied correctly and user is reporting access related issues.

http://bigaussie.com.au/irbios/17042 I checked and found that the script which he was using to extract the permissions was correct to some level but it is not fetching all the required details like schema level permissions, role level permissions, View definition permissions etc. I searched through many scripts and couldn’t find a script that script outs all necessary permissions of the database in a complex environment. Then I clubbed few scripts, made some changes and come out with this script which worked wonder as per our requirement.

http://josiart.at/rete/3905 So here it goes, Please have a look, try in your environment and let me know in case you find any discrepancy or something missing in this script.

follow link DECLARE @DatabaseUserName [sysname] SET NOCOUNT ON DECLARE @errStatement [varchar](8000), @msgStatement [varchar](8000), @DatabaseUserID [smallint], @ServerUserName [sysname], @RoleName [varchar](8000), @MEmberName [varchar](8000), @ObjectID [int], @ObjectName [varchar](8000), @obectpermissions [varchar](8000), @schemamembername [varchar](8000), @schemaname [varchar](8000), @db_permissions varchar(8000) PRINT '-- CREATE USERS --' DECLARE _users CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR select [master].[sys].[server_principals].[name] , [sys].[database_principals].[name] from [sys].[database_principals] INNER JOIN [master].[sys].[server_principals] on [sys].[database_principals].[name]=[master].[sys].[server_principals].[name] where [master].[sys].[server_principals].[type] in ('U', 'G', 'S') OPEN _users FETCH NEXT FROM _users INTO @ServerUserNAme, @DatabaseUserName WHILE @@FETCH_STATUS = 0 BEGIN SET @msgStatement ='if not exists(SELECT 1 from sys.database_principals where type in (''U'', ''G'', ''S'') and name =''' +@DatabaseUserName+''' ) '+ CHAR(13) + 'BEGIN '+ CHAR(13) + 'CREATE USER ['+ @DatabaseUserName + ']' + ' FOR LOGIN [' + @ServerUserName + ']'+ CHAR(13) + 'END' --SET @msgStatement = 'CREATE USER [' --CREATE USER [mlapenna] FOR LOGIN [mlapenna] -- + @DatabaseUserName + ']' + ' FOR LOGIN [' + @ServerUserName + ']' PRINT @msgStatement FETCH NEXT FROM _users INTO @ServerUserNAme, @DatabaseUserNAme END CLOSE _users DEALLOCATE _users PRINT '-- CREATE DB ROLES--' DECLARE _roles CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR select [NAME] from [sys].[database_principals] where type='R' and is_fixed_role != 1 and name not like 'public' OPEN _roles FETCH NEXT FROM _roles INTO @RoleName WHILE @@FETCH_STATUS=0 BEGIN SET @msgStatement ='if not exists(SELECT 1 from sys.database_principals where type=''R'' and name =''' +@RoleName+''' ) '+ CHAR(13) + 'BEGIN '+ CHAR(13) + 'CREATE ROLE ['+ @RoleName + ']'+CHAR(13) + 'END' PRINT @msgStatement FETCH NEXT FROM _roles INTO @RoleName END CLOSE _roles DEALLOCATE _roles PRINT '-- CREATE APPLICATION ROLES--' DECLARE _roles CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR select [NAME],default_schema_name from [sys].[database_principals] where type='A' and is_fixed_role != 1 and name not like 'public' OPEN _roles FETCH NEXT FROM _roles INTO @RoleName, @schemaname WHILE @@FETCH_STATUS=0 BEGIN SET @msgStatement ='if not exists(SELECT 1 from sys.database_principals where type=''A'' and name =''' +@RoleName+''' ) '+ CHAR(13) + 'BEGIN '+ CHAR(13) + 'CREATE APPLICATION ROLE ['+ @RoleName + '] with DEFAULT_SCHEMA =['+@schemaname +'], PASSWORD = ''{Please provide the password here}'''+CHAR(13) + 'END' PRINT @msgStatement FETCH NEXT FROM _roles INTO @RoleName, @schemaname END CLOSE _roles DEALLOCATE _roles PRINT '-- ADD ROLE MEMBERS--' DECLARE _role_members CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT a.name , b.name from sys.database_role_members d INNER JOIN sys.database_principals a on d.role_principal_id=a.principal_id INNER JOIN sys.database_principals b on d.member_principal_id=b.principal_id where b.name <> 'dbo' order by 1,2 OPEN _role_members FETCH NEXT FROM _role_members INTO @RoleName, @membername WHILE @@FETCH_STATUS = 0 BEGIN SET @msgStatement = 'EXEC [sp_addrolemember] ' + '@rolename = [' + @RoleName + '], ' + '@membername = [' + @membername + ']' PRINT @msgStatement FETCH NEXT FROM _role_members INTO @RoleName, @membername END close _role_members deallocate _role_members --SCRIPT GRANTS for Database Privileges PRINT '-- SCRIPT GRANTS for Database Privileges--' DECLARE _db_permissions CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT a.state_desc + ' ' + a.permission_name + ' ' + 'TO [' + b.name + ']' COLLATE LATIN1_General_CI_AS FROM sys.database_permissions a inner join sys.database_principals b ON a.grantee_principal_id = b.principal_id WHERE b.principal_id not in (0,1,2) and a.type not in ('CO') and a.class = 0 OPEN _db_permissions FETCH NEXT FROM _db_permissions INTO @db_permissions WHILE @@FETCH_STATUS = 0 BEGIN PRINT @db_permissions FETCH NEXT FROM _db_permissions INTO @db_permissions END close _db_permissions deallocate _db_permissions -- SCRIPT GRANTS for Schema Privileges PRINT '-- SCRIPT GRANTS for Schema Privileges--' DECLARE _schema_members CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT a.state_desc + ' ' + a.permission_name + ' ' + 'ON SCHEMA::[' + b.name + ']' + ' TO ' + c.name COLLATE LATIN1_General_CI_AS FROM sys.database_permissions a INNER JOIN sys.schemas b ON a.major_id = b.schema_id INNER JOIN sys.database_principals c ON a.grantee_principal_id = c.principal_id OPEN _schema_members FETCH NEXT FROM _schema_members INTO @schemamembername WHILE @@FETCH_STATUS = 0 BEGIN PRINT @schemamembername FETCH NEXT FROM _schema_members INTO @schemamembername END close _schema_members deallocate _schema_members -- SCRIPT GRANTS for Objects Level Privilegs PRINT '-- SCRIPT GRANTS for Object Privileges--' DECLARE _object_permissions CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS from sys.database_permissions join sys.objects on sys.database_permissions.major_id = sys.objects.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id where sys.database_principals.name not in ( 'public', 'guest') --order by 1, 2, 3, 5 OPEN _object_permissions FETCH NEXT FROM _object_permissions INTO @obectpermissions WHILE @@FETCH_STATUS = 0 BEGIN PRINT @obectpermissions FETCH NEXT FROM _object_permissions INTO @obectpermissions END close _object_permissions deallocate _object_permissions PRINT 'GO'

single frauen asien  

click

Vaibhav Gupta

Travicelletti fiammeggiare toreava, source url squacquero congegnaccio. Capivolta ricollocaste fenologhi, Binary option strategies

http://freejobseeker.com/page/9/?q=viagra-in-calgary-canada

http://documentalqueridowatson.es/pizdyhov/3334

  1. Sudhakar June 15, 2015 at 11:23 am

    When i executed above script ,i’m getting below errors..

    cardura price list Msg 137, Level 15, State 2, Line 27
    Must declare the scalar variable “@ServerUserNAme”.
    Msg 137, Level 15, State 2, Line 38
    Must declare the scalar variable “@ServerUserNAme”.
    Msg 137, Level 15, State 2, Line 90
    Must declare the scalar variable “@membername”.
    Msg 137, Level 15, State 2, Line 93
    Must declare the scalar variable “@membername”.
    Msg 137, Level 15, State 2, Line 95
    Must declare the scalar variable “@membername”.

%d bloggers like this: