2

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

closed as unclear what you're asking by mustaccio, John Eisbrener, John aka hot2use, Tony Hinkle, Marco Mar 25 at 15:06

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

0

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';
  • Thank you everybody for the time you spend on this for me, very appreciated :) – user175111 Mar 22 at 13:08
5

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:\temp\users.sql

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

  • is it probably Export-DbaUser? – user175111 Mar 21 at 21:11
  • One more question, can we install this in a prod server? – user175111 Mar 21 at 21:17
  • Yes it's widely used and recommended by community. As always said, test in lower environments and go to prod from there. Visit dbatools security page to get more clarification on this – KASQLDBA Mar 21 at 22:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.