CODEWITHSUNDEEP
sqlt-sqluser-permissions
JSpendley
JSpendley

Reputation: 69

T-SQL Script current user database permissions

Occasionally I have to restore DB's from our production SQL server to the test SQL instance. When the database has been restored, we manually restore the correct access permissions (e.g. DB owner/ reader/ writer) for the restored database. This process works fairly well, except for having to manually screenshot the permissions before the restore and then reapply them from the image taken.

Is there an easy way to use T-SQL to store CURRENT permissions for a user BEFORE the database restore and then reapply those same permissions once the restoration is complete?

Upvotes: 3

Views: 24441

Answers (6)

cooldug000
cooldug000

Reputation: 21

Here is a modified version of the script @Fabian wrote so that I could also script out the permissions for stored procedures. Also adds QUOTENAME so the appropriate things are in brackets.

SELECT 
    dp.permission_name collate latin1_general_cs_as    AS Permission,
    t.TABLE_SCHEMA + '.' + o.name AS TableName,
    rt.ROUTINE_SCHEMA + '.' + o.name AS ProcedureName,
    dpr.name AS Username
    , 'GRANT ' + dp.permission_name collate latin1_general_cs_as 
        + ' ON ' 
        + QUOTENAME(CASE WHEN t.TABLE_SCHEMA IS NOT NULL THEN t.TABLE_SCHEMA ELSE rt.ROUTINE_SCHEMA END )
        + '.' 
        + QUOTENAME(o.name)
        + ' TO ' 
        +  QUOTENAME(dpr.name)
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
LEFT outer JOIN INFORMATION_SCHEMA.TABLES t ON  TABLE_NAME = o.name              
LEFT OUTER JOIN INFORMATION_SCHEMA.ROUTINES rt ON rt.ROUTINE_NAME = o.name
WHERE dpr.name NOT IN ('public','guest')
ORDER BY Permission, TableName, ProcedureName, Username

Upvotes: 2

Fabian
Fabian

Reputation: 348

The answer to your question is most probably the one from Jeff.

But the script from Howard is very practical, i just added a column, which generates the TSQL Syntax with the information. You can copy that and run as SQL to 'replicate' permissions to another db.

SELECT 
dp.permission_name collate latin1_general_cs_as    AS Permission,
t.TABLE_SCHEMA + '.' + o.name AS Object,
dpr.name AS Username
, 'GRANT ' + dp.permission_name collate latin1_general_cs_as 
    + ' ON ' 
    + t.TABLE_SCHEMA 
    + '.' 
    + o.name 
    + ' TO ' 
    +  dpr.name
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
INNER JOIN INFORMATION_SCHEMA.TABLES t
    ON  TABLE_NAME = o.name                
WHERE dpr.name NOT IN ('public','guest')
ORDER BY
   Permission, Object,Username

Upvotes: 3

Paul
Paul

Reputation: 1059

Combine this with the answer regarding permissions:

SELECT 'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '')
+ ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '') 
AS 'Role Memberships'
FROM sys.database_role_members AS rm
ORDER BY rm.role_principal_id

Upvotes: 0

Howard Rothenburg
Howard Rothenburg

Reputation: 1348

SELECT 
dp.permission_name collate latin1_general_cs_as    AS Permission,
t.TABLE_SCHEMA + '.' + o.name AS Object,
dpr.name AS Username
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
INNER JOIN INFORMATION_SCHEMA.TABLES t
        ON  TABLE_NAME = o.name                
WHERE dpr.name NOT IN ('public','guest')
ORDER BY
  Permission, Object,Username

Upvotes: 1

Jeff S
Jeff S

Reputation: 7484

The problem you are dealing with is known as orphaned users.

Here's a script I've used in the past (I had to put this together from memory to you should verify it carefully):

create table #users (UserName sysname, UserSID varbinary(85))

Insert into #users
exec sp_change_users_login @Action='Report';

declare mycursor cursor for select * from #users;
open mycursor;

declare @UserName sysname;
declare @UserSID varbinary(85);

fetch next from mycursor
into @UserName, @UserSID

while @@FETCH_STATUS = 0
begin
    exec  sp_change_users_login @Action='update_one', @UserNamePattern=@UserName, @LoginName=@UserName;
end
close mycursor;
deallocate mycursor;

This script makes one important assumption. That the users login name and their user name in the database match. If that isn't true, you will have to change the @LoginName parameter sent to sp_change_users_login.

Upvotes: 0

AdamL
AdamL

Reputation: 13141

There's a very useful function: sys.fn_my_permissions ( securable , 'securable_class' ) It enables you to see EFFECTICVE permissions of current user to specified objects, so I don't know if you can simply build GRANT/DENY commands from it. I never used it that way. In your case you'd run it as another user:

EXECUTE AS USER = '<username>';
GO
SELECT *
FROM fn_my_permissions(null, 'SERVER') 
GO

SELECT *
FROM fn_my_permissions('<DBNAME>', 'Database')
ORDER BY subentity_name, permission_name ;

REVERT;
GO

Upvotes: 1

Related Questions