Click here to Skip to main content
15,617,116 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In SQL Server, if I have a view in a contained database and wish to grant SELECT on this view ONLY in the database to a new user, is the best approach to create the user in TQSL and the Grant SELECT on the view? Does creating a user in TSQL like below give them zero permissions initially?

USE Database_Name
CREATE USER User_Name WITH PASSWORD = 'apassword';

Then to give them ONLY select permissions on this ONE view

USE Database_Name
GRANT SELECT ON OBJECT::[dbo].[theView] TO User_Name

Thanks in advance

What I have tried:

Googled question but a few conflicting answers such as apply db_datareader etc
Updated 24-Jun-21 10:00am
SeeSharp2 24-Jun-21 15:41pm    
That looks right. The easiest way to find out is to just do it and see what happens.

1 solution

This is actually quite broad subject. First of all when you create a user, it will not have any initial unless privileges have been granted to public role. Every user is member of public.

Also keep in mind that public role exists in every database but also as a server wide role. So all privileges granted to either the server role or the public role in the database user is accessing are effective to the user.

The next thing is the privilege you want to grant. While you can grant the privilege directly to the user, I would strongly recommend using roles. Most likely you have a bunch of objects you want to grant access to certain group of users. In such case if you create a role and grant privileges to that role, you can easily grant privileges to users just by granting a role to them.

When you use role, you don't have to create a role for each unique privilege combination because roles are cumulative. If several rows has been granted to a user, the privileges for that user is the sum of privileges from all granted roles.

One thing that is good to know is that typically roles grant privileges but they can also deny privileges. In certain situations it makes sense that roles are granted to people and then some of the given privileges are denied from those users. Deny always overrides grant so even if privileges are accumulated from all granted roles, deny is more powerful. But to keep things simple, I would suggest avoiding deny unless you really need it. It's often much easier to manage and understand the privileges when they are granted using small enough roles .

Few links you may find useful:
- Server and Database Roles in SQL Server | Microsoft Docs[^]
- Server-Level Roles - SQL Server | Microsoft Docs[^]
- CREATE ROLE (Transact-SQL) - SQL Server | Microsoft Docs[^]
- GRANT Object Permissions (Transact-SQL) - SQL Server | Microsoft Docs[^]
- DENY (Transact-SQL) - SQL Server | Microsoft Docs[^]
Share this answer
jar8695 24-Jun-21 17:11pm    
Thanks Wendelius. So, if I check the public role has NO privileges then a CREATE USER statement will mean that user has no privileges until granted to them - correct?
Then if I create a role, add the new user to the role and grant SELECT on the specific view to the role then this will limit the user to only have SELECT on that view and provide better control of granting and revoking additional privileges - is that correct?
Wendelius 24-Jun-21 17:27pm    
Yes, just remember to check both public roles, the server role and the one in the database. If they contain no privileges and no other roles are granted to the user then no privileges should be active until you grant new roles.

Of course, it's always good practice to test and verify the situation :)
jar8695 24-Jun-21 17:37pm    
Okay. I found this script for checking Public role

USE [Databasename] -- Specify database name

WITH [PublicRoleDBPermissions]
AS (
SELECT p.[state_desc] AS [PermissionType]
,p.[permission_name] AS [PermissionName]
,USER_NAME(p.[grantee_principal_id]) AS [DatabaseRole]
,CASE p.[class]
THEN 'Database::' + DB_NAME()
THEN 'Schema::' + SCHEMA_NAME(p.[major_id])
END AS [ObjectName]
FROM [sys].[database_permissions] p
WHERE p.[class] IN (0, 1, 3)
AND p.[minor_id] = 0
SELECT [PermissionType]
,SCHEMA_NAME(o.[schema_id]) AS [ObjectSchema]
,o.[type_desc] AS [ObjectType]
,[PermissionType] + ' ' + [PermissionName] + ' ON ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME([ObjectName]) + ' TO ' + QUOTENAME([DatabaseRole]) AS [GrantPermissionTSQL]
,'REVOKE' + ' ' + [PermissionName] + ' ON ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME([ObjectName]) + ' TO ' + QUOTENAME([DatabaseRole]) AS [RevokePermissionTSQL]
FROM [PublicRoleDBPermissions] p
INNER JOIN [sys].[objects] o
ON o.[name] = p.[ObjectName]
AND OBJECTPROPERTY(o.object_id, 'IsMSShipped') = 0
WHERE [DatabaseRole] = 'Public'
ORDER BY [DatabaseRole]
Wendelius 25-Jun-21 2:51am    
It probably would be easier and more safe to use builtin function HAS_PERMS_BY_NAME. See HAS_PERMS_BY_NAME (Transact-SQL) - SQL Server | Microsoft Docs[^]
Maciej Los 25-Jun-21 2:22am    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900