Script To generate the script of Database Users, roles and object level permissions in SQL Server 2005

-- Script To generate the script of Database Users, roles and object level permissions in SQL Server 2005.

SET NOCOUNT ON

DECLARE     @OldUser varchar(100), @NewUser sysname

DECLARE a_cur CURSOR FOR

select name,name from sys.sysusers where uid>3 AND uid < 16384 ORDER BY

      1

Open a_cur

FETCH NEXT from a_cur into @OldUser,@NewUser

--SET @NewUser = @OldUser

WHILE (@@FETCH_STATUS = 0)

BEGIN

SELECT      'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'

SELECT      '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'

SELECT      'EXEC sp_addrolemember @rolename ='

      + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'

FROM  sys.database_role_members AS rm

WHERE USER_NAME(rm.member_principal_id) = @OldUser

ORDER BY rm.role_principal_id ASC

SELECT      CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

      + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)

      + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

      + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

      + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM  sys.database_permissions AS perm

      INNER JOIN

      sys.objects AS obj

      ON perm.major_id = obj.[object_id]

      INNER JOIN

      sys.database_principals AS usr

      ON perm.grantee_principal_id = usr.principal_id

      LEFT JOIN

      sys.columns AS cl

      ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

WHERE usr.name = @OldUser

ORDER BY perm.permission_name ASC, perm.state_desc ASC

SELECT      CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

      + SPACE(1) + perm.permission_name + SPACE(1)

      + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

      + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'

FROM  sys.database_permissions AS perm

      INNER JOIN

      sys.database_principals AS usr

      ON perm.grantee_principal_id = usr.principal_id

WHERE usr.name = @OldUser

AND   perm.major_id = 0

ORDER BY perm.permission_name ASC, perm.state_desc ASC

FETCH NEXT from a_cur into @OldUser,@NewUser

END

CLOSE a_cur

DEALLOCATE a_cur

List of All tables and Foreign Keys

SELECT         (CASE

         WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'

         ELSE 'DISABLED'

        END) AS STATUS,

         OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,

         OBJECT_NAME(FKEYID) AS TABLE_NAME,

        COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,

         OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,

         COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME

FROM SYSFOREIGNKEYS

ORDER BY  TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME,  KEYNO

GO

ATTACH DB &&& ATTACH SINGLEFILE TO DB

ATTACH DB:

Use master

Go

Sp_attach_db ‘pubs’,’E: \Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'’

EXEC sp_attach_db @dbname = N'pubs',

   @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',

   @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

ATTACH SINGLEFILE TO DB:

EXEC sp_detach_db @dbname = 'pubs'

EXEC sp_attach_single_file_db @dbname = 'pubs',

@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'