Permission on Database [closed]

I have a login user called TestUser created at the server level that is assigned to a read only role, now I need to create a user for the TestUser login on MyDb

USE [MyDB]
GO
CREATE USER [Test] FOR LOGIN [TestUser]
GO
GRANT SELECT TO [Test]
GO
DENY DELETE TO [Test]
GO
DENY INSERT TO [Test]
GO
DENY UPDATE TO [Test]
GO

everything is good but when I script that Test user created, I don’t see in any place the script that I’m giving select and denying insert, delete and update

I have a login user called TestUser created at the server level that
is assigned to a read only role

There is no any fixed server role that can be called “read only role”.

Maybe your @@version >= 2014 and you granted select all user securables to your login?

If so, this server level permission can be seen this way:

select class_desc, permission_name, state_desc
from sys.server_permissions
where suser_name(grantee_principal_id) = 'Test';
---------------------------------------------------
--class_desc    permission_name state_desc
--SERVER    CONNECT SQL GRANT
--SERVER    SELECT ALL USER SECURABLES  GRANT

Then you did some unnecessary grant/deny, you should not did it. When you map your login that has select all user securables this user already access any data in this database, so there is no need in additional SELECT permission.

Also there is no need to deny other rights as the newly created user does not have any of this permission.

But if you want to check that they were granted/denied you should select from sys.database_permissions like so:

select class_desc, permission_name, state_desc
from sys.database_permissions
where user_name(grantee_principal_id) = 'Test';
---------------------------------------------------
--class_desc    permission_name state_desc
--DATABASE  CONNECT GRANT
--DATABASE  DELETE  DENY
--DATABASE  INSERT  DENY
--DATABASE  SELECT  GRANT
--DATABASE  UPDATE  DENY

You can script server level permissions as well as database level permissions using sys.server_permissions / sys.database_permissions, in your simple case when the securable is database the script can look like this:

select state_desc +'  ' + permission_name + ' to Test'
from sys.database_permissions
where user_name(grantee_principal_id) = 'Test'
      and permission_name <> 'connect';

Here is the complete script to test:

create login Test with password = '*****', check_policy = off;
grant select all user securables to Test;

select class_desc, permission_name, state_desc
from sys.server_permissions
where suser_name(grantee_principal_id) = 'Test';
---------------------------------------------------
--class_desc    permission_name state_desc
--SERVER    CONNECT SQL GRANT
--SERVER    SELECT ALL USER SECURABLES  GRANT

use MyDB;
create user Test from login Test;

select class_desc, permission_name, state_desc
from sys.database_permissions
where user_name(grantee_principal_id) = 'Test';
---------------------------------------------------
--class_desc    permission_name state_desc
--DATABASE  CONNECT GRANT

GRANT SELECT TO [Test]
GO
DENY DELETE TO [Test]
GO
DENY INSERT TO [Test]
GO
DENY UPDATE TO [Test]
GO

select class_desc, permission_name, state_desc
from sys.database_permissions
where user_name(grantee_principal_id) = 'Test';
---------------------------------------------------
--class_desc    permission_name state_desc
--DATABASE  CONNECT GRANT
--DATABASE  DELETE  DENY
--DATABASE  INSERT  DENY
--DATABASE  SELECT  GRANT
--DATABASE  UPDATE  DENY

select state_desc +'  ' + permission_name + ' to Test'
from sys.database_permissions
where user_name(grantee_principal_id) = 'Test'
      and permission_name <> 'connect';

Most probably because you are using the generate scripts method to do so.

I would suggest to use open source DBA tools to view the permission or export.

Aliases : Export-SqlUser

Synopsis

Exports users creation and its permissions to a T-SQL file or host.

Description

Exports users creation and its permissions to a T-SQL file or host. Export includes user, create and add to role(s), database level permissions, object level permissions.

Example:

PS C:> Export-DbaUser -SqlInstance sqlserver2014a -User User1, User2
-Path C:tempusers.sql

Exports ONLY users User1 and User2 from sqlserver2014a to the file C:tempusers.sql