Script DB Level Permissions

use master
go
Create View VUserRights
as
SELECT top 100 percent
U.[Name] as UserName
,O.Name as ObjectName
,case xtype
when 'S' then 'System Table'
when 'P' then 'Stored Procedure'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'L' then 'Log'
when 'FN' then 'Scalar Function'
when 'IF' then 'Inlined Table-Function'
when 'PK' then 'PRIMARY KEY'
when 'RF' then 'Replication Filter Stored Procedure'
when 'S' then 'System Table'
when 'TF' then 'Table Function'
when 'TR' then 'Trigger'
when 'U' then 'User Table'
when 'UQ' then 'UNIQUE Constraint'
when 'V' then 'View'
when 'X' then 'Extended Stored Procedure'
else cast(xtype as varchar(30))
end as XType

,Case p.[action]
When 26 then 'REFERENCES'
When 178 then 'CREATE FUNCTION'
When 193 then 'SELECT'
When 195 then 'INSERT'
When 196 then 'DELETE'
When 197 then 'UPDATE'
When 198 then 'CREATE TABLE'
When 203 then 'CREATE DATABASE'
When 207 then 'CREATE VIEW'
When 222 then 'CREATE PROCEDURE'
When 224 then 'EXECUTE'
When 228 then 'BACKUP DATABASE'
When 233 then 'CREATE DEFAULT'
When 235 then 'BACKUP LOG'
When 236 then 'CREATE RULE'
Else cast([Action] as varchar(20))
End as 'Action'

,Case p.protecttype
When 204 Then 'GRANT_W_GRANT'
When 205 Then 'GRANT'
When 206 Then 'REVOKE'
Else cast(protecttype as varchar(20))
end as ProtectType

FROM sysusers U join sysprotects P
on u.uid = P.uid
Join sysobjects O
on P.id = O.id
where xtype <>'s'
order by U.uid ASC, O.xtype Desc

--drop view VUserRights

/*
Here are some calling statements
--2 is an oracle trick that i learned to
create a permissions assignment statement from exisiting metadata
*/

GO
--1
--select * from vuserrights
--Go
--2
-- select Protecttype + ' ' + Action + ' ON ' + Oname
-- +'('+ Xtype+')'
-- + ' TO ' + Uname from vuserRights
-- Reply With Quote

--select * from sysobjects where name like 'ALS_AppLetterRefNos%'
--select * from sysdatabases where name like 'ALS%'
--select * from vuserrights

CREATE TABLE #t(d SYSNAME, t SYSNAME);

EXEC sp_msForEachDB 'INSERT #t SELECT ''?'', TABLE_NAME
FROM [?].INFORMATION_SCHEMA.TABLES
--WHERE TABLE_TYPE=''BASE TABLE''
;';

select #t.D DBname, V.* from #t right join vuserrights V on #t.t=V.Objectname
--select * from vuserrights

--select * from vuserrights where Oname NOT EXISTS(select #t.D, #t.t, V.* from #t right join vuserrights V on #t.t=V.Oname)

--SELECT * FROM #t ORDER BY d,t;

--select * from INFORMATION_SCHEMA.TABLES

DROP TABLE #t;

--Create a Login and a corresponding user
USE master
GO
CREATE LOGIN aG WITH PASSWORD = 'somepwd',CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF

USE AdventureWorks
GO
CREATE USER aG FOR LOGIN aG
GO

--Let us try authenticating as aG and see the results
USE AdventureWorks
GO
EXECUTE AS USER = 'aG'
SELECT TOP 10 * FROM Sales.Customer
/*Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'Customer', database 'AdventureWorks', schema 'Sales'.*/

REVERT

--Grant explicit privileges
GRANT SELECT ON SCHEMA::[Sales] TO [aG]
GO

--Authenticate again using aG
EXECUTE AS USER = 'aG'
SELECT TOP 10 * FROM Sales.Customer

--You should be able to select the top 10 records. (just to make sure the permissions are granted)
--Now Run the scripts

REVERT

--Existing script
USE AdventureWorks
GO
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'TO' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
+ QUOTENAME(SCHEMA_NAME(grantee_principal_id))
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.grantee_principal_id = s.schema_id
WHERE class = 3

-- SQL STATEMENTS -- -- RESULT ORDER HOLDER --
----------------------------------------------------------------------------
--GRANT SELECT TO SCHEMA::[HumanResources] 10

--Updated script
USE AdventureWorks
GO
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
+ QUOTENAME(SCHEMA_NAME(major_id))
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(grantee_principal_id) + ']' COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
10 AS [-- RESULT ORDER HOLDER --]
from sys.database_permissions AS perm
inner join sys.schemas s
on perm.grantee_principal_id = s.schema_id
WHERE class = 3

-- SQL STATEMENTS -- -- RESULT ORDER HOLDER --
----------------------------------------------------------------------------
--GRANT SELECT ON SCHEMA::[Sales] TO [ag] 10

--You can still do the same thing using GUI.
--Navigate through Instance name > databasename > Security > Schemas > Properties > Permission tab
--Grant/revoke explict privileges. Run the scripts again.

--I have verified with 'WITH GRANT OPTION'; also for Windows authenticated login, works fine for me.