A
While having SELECT (or another permission) on the entire database implies having SELECT on an object within the database, having the GRANT OPTION for SELECT on the entire database does not imply you have the GRANT OPTION for SELECT on an object in the database.
Correct. There is a difference between "actual" / "explicit" permissions and "effective" permissions. Granting SELECT at the Database-level is a distinctly different permission than granting SELECT on one or more objects within that Database. You would see this difference when looking in the System Catalog View sys.database_permissions. Hence, SELECT permissions on individual tables is not an actual subset of the Database-level SELECT permission. The same holds true for SELECT permission on a Table as opposed to SELECT permission on individual columns in that Table.
Now, while it would help to get some feedback / clarification on the overall goal of this request, it at least appears -- based on the example code in the question -- that the desire is to have a deployment / rollout Login (or Logins) that are not in the sysadmin fixed server role. To that end, you can still accomplish this type of setup using Module Signing. The idea is to create a Certificate and Certificate-based Login and associated User that cannot be Impersonated (i.e. can't log in or become current Login/User via EXECUTE AS) but can hold all of the required permissions necessary to carry out the tasks that the deployment process needs to do. Then, you create one or more Stored Procedures that will execute the required commands. Signing the Stored Procedure(s) with the same Certificate that was used to create the Login and User will allow the permissions granted to the Certificate-based Login and User to apply to code within the Stored Procedure. Given the dynamic nature of the operation, it will need to be done via Dynamic SQL, but this works just fine with Module Signing.
This approach allows you to have a generic set of permissions (assigned to the Certificate-based Login / User) that can be used to assign other permissions. For the moment, it seems easiest to put the User in the db_owner fixed database role as that allows for doing anything you would need to do. And since Module Signing gives the permissions to the code, not the Login / User / Role executing the module (which is how it works with Impersonation via EXECUTE AS), anyone who can execute that code can only ever do what the code is set up to do. In this way, Module Signing allows for much more granular control over extending permissions than Impersonation (it doesn't matter what the permissions granted to the Certificate-based Login / User can do, it only matters what the signed code actually does). And if someone changes that signed code, then the module loses the signature, alerting you to the change, at which time you can review the changes and if ok, then re-sign the module.
Please see the example below that allows for granting SELECT on a particular object to a given role. It is very easy to extend the example to allow for creating the new Role as well as anything else that needs to be done as part of the deployment process.
Solution Setup, Part 1 (General Supporting Structure)
USE [master];
GO
CREATE CERTIFICATE [DeploymentPermissionsKey]
ENCRYPTION BY PASSWORD = 'not_really_a_password'
WITH SUBJECT = 'Deployment Permissions';
CREATE LOGIN [DeploymentPermissions]
FROM CERTIFICATE [DeploymentPermissionsKey];
GRANT VIEW SERVER STATE TO [DeploymentPermissions];
DECLARE @Certificate VARBINARY(2000),
@PrivateKey VARBINARY(2000);
SELECT @Certificate = CERTENCODED(CERT_ID(N'DeploymentPermissionsKey')),
@PrivateKey = CERTPRIVATEKEY(CERT_ID(N'DeploymentPermissionsKey'),
'EncryptPass', 'not_really_a_password');
---
USE [tempdb];
--DROP CERTIFICATE [DeploymentPermissionsKey];
DECLARE @CertSQL NVARCHAR(MAX) = N'
CREATE CERTIFICATE [DeploymentPermissionsKey]
FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @Certificate, 1) + N'
WITH PRIVATE KEY ( BINARY = ' + CONVERT(NVARCHAR(MAX), @PrivateKey, 1) + N',
DECRYPTION BY PASSWORD = ''EncryptPass'',
ENCRYPTION BY PASSWORD = ''not_really_a_password''
);
';
PRINT @CertSQL;
EXEC(@CertSQL);
--DROP ROLE [DeploymentRole];
CREATE ROLE [DeploymentRole];
--DROP USER [MrDeploy];
CREATE USER [MrDeploy] WITHOUT LOGIN;
ALTER ROLE [DeploymentRole] ADD MEMBER [MrDeploy];
--DROP USER [DeploymentPermissions];
CREATE USER [DeploymentPermissions]
FROM LOGIN [DeploymentPermissions];
ALTER ROLE [db_owner] ADD MEMBER [DeploymentPermissions];
Solution Setup, Part 2 (Allow Granting Permission to Role)
Please note that due to needing to use Dynamic SQL, we need to protect against SQL Injection by checking / verifying the string input parameters: @RoleName and @ObjectName.
USE [tempdb];
GO
CREATE
--ALTER
PROCEDURE dbo.GrantObjectPermissionsToRole
(
@RoleName [sysname],
@ObjectName NVARCHAR(261),
@PermissionID TINYINT
)
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
-- Check parameter values to prevent errors and SQL Injection:
IF (NOT EXISTS(
SELECT dp.*
FROM sys.database_principals dp
WHERE dp.[type] = 'R' -- DATABASE_ROLE
AND dp.[name] = @RoleName
)
)
BEGIN
RAISERROR(N'Role does not exist: %s', 16, 1, @RoleName);
RETURN -1;
END;
IF (OBJECT_ID(@ObjectName) IS NULL)
BEGIN
RAISERROR(N'Object does not exist: %s', 16, 1, @ObjectName);
RETURN -2;
END;
SET @SQL = N'GRANT ' + CASE @PermissionID
WHEN 1 THEN N'SELECT'
WHEN 2 THEN N'EXECUTE'
ELSE N'Invalid_PermissionID_'
+ CONVERT(NVARCHAR(10), @PermissionID)
END
+ N' ON ' + @ObjectName
+ N' TO ' + @RoleName
+ N' AS [dbo];';
PRINT @SQL; -- debug
EXEC(@SQL);
RETURN;
GO
GRANT EXECUTE ON dbo.[GrantObjectPermissionsToRole] TO [DeploymentRole];
ADD SIGNATURE TO dbo.[GrantObjectPermissionsToRole]
BY CERTIFICATE [DeploymentPermissionsKey]
WITH PASSWORD = 'not_really_a_password';
Example Setup
USE [tempdb];
GO
CREATE TABLE [dbo].[TestTable] ([ID] INT NULL);
CREATE ROLE [TestRole];
CREATE USER [TestUser] WITHOUT LOGIN;
ALTER ROLE [TestRole] ADD MEMBER [TestUser];
Tests
Test 1 shows that initially, TestUser, who is in TestRole, does not have permission to SELECT from TestTable.
Test 2 shows that the deployment User, MrDeploy, also does not have SELECT permission on TestTable. Additionally, MrDeploy does not even have the ability to grant any permissions to TestRole. However, MrDeploy can execute the Stored Procedure which does grant the SELECT permission to TestRole.
Test 3 shows that TestUser, via its membership in TestRole, now does have SELECT permission on TestTable. Using sys.fn_my_permissions() we can see the "effective" permissions which cascade down to individual columns. However, selecting from sys.database_permissions we can see the "actual" permission which is just on the Table itself (showing that actual permissions do not cascade).
Test 4 grants the Database-level SELECT permission to MrDeploy so that it can be tested against the sys.fn_my_permissions() functions (to see how the permission cascades down to subobjects) and the sys.database_permissions system catalog view (to see that there are no object-level permissions at all). This again illustrates the difference between "effective" and "actual" / "explicit" permissions.
--- Test # 1 ---
EXECUTE AS USER = 'TestUser';
SELECT SESSION_USER AS [CurrentUser];
SELECT * FROM [dbo].[TestTable]; -- error:
-- The SELECT permission was denied on the object 'TestTable',
-- database 'tempdb', schema 'dbo'.
REVERT;
SELECT SESSION_USER AS [CurrentUser];
--- Test # 2 ---
EXECUTE AS USER = 'MrDeploy';
SELECT SESSION_USER AS [CurrentUser];
SELECT * FROM [dbo].[TestTable]; -- error:
-- The SELECT permission was denied on the object 'TestTable',
-- database 'tempdb', schema 'dbo'.
GRANT SELECT ON [dbo].[TestTable] TO [TestUser]; -- error:
-- Cannot find the object 'TestTable', because it does not exist or you
-- do not have permission.
GRANT SELECT ON [dbo].[TestTable] TO [TestUser] AS [dbo]; -- error:
-- Cannot find the user 'dbo', because it does not exist or you do not have permission.
SELECT dp.*
FROM sys.database_principals dp
WHERE dp.[type] = 'R' -- DATABASE_ROLE
AND dp.[name] = N'TestRole'; -- no rows due to no VIEW SERVER STATE permission
EXEC dbo.[GrantObjectPermissionsToRole]
@RoleName = N'TestRole',
@ObjectName = N'TestTable',
@PermissionID = 1;
-- Success!
SELECT * FROM [dbo].[TestTable]; -- error (still):
-- The SELECT permission was denied on the object 'TestTable',
-- database 'tempdb', schema 'dbo'.
REVERT;
SELECT SESSION_USER AS [CurrentUser];
--- Test # 3 ---
EXECUTE AS USER = 'TestUser';
SELECT SESSION_USER AS [CurrentUser];
SELECT * FROM [dbo].[TestTable]; -- Success!!
SELECT * FROM sys.fn_my_permissions(N'dbo.TestTable', 'OBJECT');
SELECT *,
USER_NAME([grantee_principal_id]) AS [Grantee],
USER_NAME([grantor_principal_id]) AS [Grantor]
FROM sys.database_permissions
WHERE [class] = 1 -- OBJECT_OR_COLUMN
AND [major_id] = OBJECT_ID(N'dbo.TestTable');
REVERT;
SELECT SESSION_USER AS [CurrentUser];
--- Test # 4 ---
GRANT SELECT TO [MrDeploy];
EXECUTE AS USER = 'MrDeploy';
SELECT SESSION_USER AS [CurrentUser];
SELECT * FROM sys.fn_my_permissions(N'dbo.TestTable', 'OBJECT');
SELECT *,
USER_NAME([grantee_principal_id]) AS [Grantee],
USER_NAME([grantor_principal_id]) AS [Grantor]
FROM sys.database_permissions
WHERE [class] = 1 -- OBJECT_OR_COLUMN
AND [major_id] = OBJECT_ID(N'dbo.TestTable');
REVERT;
SELECT SESSION_USER AS [CurrentUser];
