I want to generate a script to assign a user account to some securables, e.g. Table:Select.
How to do this?
How to automatically script the securables assigned to a SQL account?
Asked Answered
you have the user assigned permissions to the securables already & you want to generate the grant statements? –
Abiosis
yes, but automatically. Just like "Script>Create Table" –
Hedgepeth
This has been answered in: #7049339 –
Syphilology
I had the same problem and solved it with Justins suggestion.
- Right-click a template/example user in the database hive.
- Choose
properties,securables. - Make a change
- Do Ctrl+Shift+N to get a script of the change.
This work great for scripting new changes but not for scripting existing value. :( –
Stipulation
Link from RYU is dead.
Found another script that does the job here:
http://blogs.msdn.com/b/blogdoezequiel/archive/2010/04/26/the-sql-swiss-army-knife-1.aspx
In case it ever dies here it is:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_SecurCreation]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_SecurCreation]
GO
CREATE PROCEDURE usp_SecurCreation @User NVARCHAR(128) = NULL, @DB NVARCHAR(256) = NULL
WITH ENCRYPTION
AS
--
-- Does not deal with CERTIFICATE_MAPPED_LOGIN and ASYMMETRIC_KEY_MAPPED_LOGIN types
--
-- All users: EXEC usp_SecurCreation
--
-- One user, All DBs: EXEC usp_SecurCreation '<User>'
--
-- One user, One DBs: EXEC usp_SecurCreation '<User>', '<DBName>'
--
-- All users, One DBs: EXEC usp_SecurCreation NULL, '<DBName>'
--
SET NOCOUNT ON
DECLARE @SC NVARCHAR(4000), @SCUser NVARCHAR(4000), @SCDB NVARCHAR(4000)
CREATE TABLE #TempSecurables ([State] VARCHAR(100),
[State2] VARCHAR(100),
[PermName] VARCHAR(100),
[Type] NVARCHAR(60),
[Grantor] VARCHAR(100),
[User] VARCHAR(100)
)
CREATE TABLE #TempSecurables2 ([DBName] sysname,
[State] VARCHAR(1000)
)
IF @User IS NULL AND @DB IS NULL
BEGIN
--Server level Privileges to User or User Group
INSERT INTO #TempSecurables
SELECT CASE CAST(p.state AS VARCHAR(100)) WHEN 'D' THEN 'DENY' WHEN 'R' THEN 'REVOKE' WHEN 'G' THEN 'GRANT' WHEN 'W' THEN 'GRANT' END,
CASE CAST(p.state AS VARCHAR(100)) WHEN 'W' THEN 'WITH GRANT OPTION' ELSE '' END, CAST(p.permission_name AS VARCHAR(100)), RTRIM(p.class_desc),
(SELECT [name] FROM sys.server_principals WHERE principal_id = p.grantor_principal_id), CAST(l.name AS VARCHAR(100))
FROM sys.server_permissions p JOIN sys.server_principals l
ON p.grantee_principal_id = l.principal_id
AND l.is_disabled = 0
AND l.type IN ('S', 'U', 'G', 'R')
INSERT INTO #TempSecurables2
EXEC master.dbo.sp_MSforeachdb @command1='USE [?]
--Privileges for Procedures/Functions/CLR/Views to the User
SELECT ''[?]'', CASE WHEN (b.state_desc COLLATE database_default) = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE (b.state_desc COLLATE database_default) END + '' EXECUTE ON ['' + c.name + ''].['' + a.name + ''] TO '' + QUOTENAME(USER_NAME(b.grantee_principal_id)) +
CASE STATE WHEN ''W'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM sys.all_objects a, sys.database_permissions b, sys.schemas c
WHERE a.OBJECT_ID = b.major_id AND a.type IN (''X'',''P'',''FN'',''AF'',''FS'',''FT'') AND b.grantee_principal_id <>0
AND b.grantee_principal_id <>2 AND a.schema_id = c.schema_id
ORDER BY c.name
--Table and View Level Privileges to the User
SELECT ''[?]'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] TO ['' + grantee + '']'' +
CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE <> ''public''
--Column Level Privileges to the User
SELECT ''[?]'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] ('' + column_name + '') TO ['' + grantee + '']'' +
CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTEE <> ''public'''
END
ELSE IF @User IS NULL AND @DB IS NOT NULL
BEGIN
--Server level Privileges to User or User Group
INSERT INTO #TempSecurables
SELECT CASE CAST(p.state AS VARCHAR(100)) WHEN 'D' THEN 'DENY' WHEN 'R' THEN 'REVOKE' WHEN 'G' THEN 'GRANT' WHEN 'W' THEN 'GRANT' END,
CASE CAST(p.state AS VARCHAR(100)) WHEN 'W' THEN 'WITH GRANT OPTION' ELSE '' END, CAST(p.permission_name AS VARCHAR(100)), RTRIM(p.class_desc),
(SELECT [name] FROM sys.server_principals WHERE principal_id = p.grantor_principal_id), CAST(l.name AS VARCHAR(100))
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
AND l.is_disabled = 0
AND l.type IN ('S', 'U', 'G', 'R')
SET @SCDB='USE [' + @DB + ']
--Privileges for Procedures/Functions/CLR/Views to the User
SELECT ''[' + @DB + ']'', CASE WHEN (b.state_desc COLLATE database_default) = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE (b.state_desc COLLATE database_default) END + '' EXECUTE ON ['' + c.name + ''].['' + a.name + ''] TO '' + QUOTENAME(USER_NAME(b.grantee_principal_id)) +
CASE STATE WHEN ''W'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM sys.all_objects a, sys.database_permissions b, sys.schemas c
WHERE a.OBJECT_ID = b.major_id AND a.type IN (''X'',''P'',''FN'',''AF'',''FS'',''FT'') AND b.grantee_principal_id <>0
AND b.grantee_principal_id <>2 AND a.schema_id = c.schema_id
ORDER BY c.name
--Table and View Level Privileges to the User
SELECT ''[' + @DB + ']'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] TO ['' + grantee + '']'' +
CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE <> ''public''
--Column Level Privileges to the User
SELECT ''[' + @DB + ']'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] ('' + column_name + '') TO ['' + grantee + '']'' +
CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTEE <> ''public'''
INSERT INTO #TempSecurables2
EXEC master..sp_executesql @SCDB
END
ELSE IF @User IS NOT NULL AND @DB IS NOT NULL
BEGIN
--Server level Privileges to User or User Group
INSERT INTO #TempSecurables
SELECT CASE CAST(p.state AS VARCHAR(100)) WHEN 'D' THEN 'DENY' WHEN 'R' THEN 'REVOKE' WHEN 'G' THEN 'GRANT' WHEN 'W' THEN 'GRANT' END,
CASE CAST(p.state AS VARCHAR(100)) WHEN 'W' THEN 'WITH GRANT OPTION' ELSE '' END, CAST(p.permission_name AS VARCHAR(100)), RTRIM(p.class_desc),
(SELECT [name] FROM sys.server_principals WHERE principal_id = p.grantor_principal_id), CAST(l.name AS VARCHAR(100))
FROM sys.server_permissions AS p JOIN sys.server_principals AS l
ON p.grantee_principal_id = l.principal_id
AND l.is_disabled = 0
AND l.type IN ('S', 'U', 'G', 'R')
AND QUOTENAME(l.name) = QUOTENAME(@User)
SET @SCDB='USE [' + @DB + ']
--Privileges for Procedures/Functions/CLR/Views to the User
SELECT ''[' + @DB + ']'', CASE WHEN (b.state_desc COLLATE database_default) = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE (b.state_desc COLLATE database_default) END + '' EXECUTE ON ['' + c.name + ''].['' + a.name + ''] TO '' + QUOTENAME(USER_NAME(b.grantee_principal_id)) +
CASE STATE WHEN ''W'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM sys.all_objects a, sys.database_permissions b, sys.schemas c
WHERE a.OBJECT_ID = b.major_id AND a.type IN (''X'',''P'',''FN'',''AF'',''FS'',''FT'') AND b.grantee_principal_id <>0
AND b.grantee_principal_id <>2 AND a.schema_id = c.schema_id
AND QUOTENAME(USER_NAME(b.grantee_principal_id)) = ''[' + @User + ']''
ORDER BY c.name
--Table and View Level Privileges to the User
SELECT ''[' + @DB + ']'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] TO ['' + grantee + '']'' +
CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE <> ''public''
AND grantee = ''[' + @User + ']''
--Column Level Privileges to the User
SELECT ''[' + @DB + ']'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] ('' + column_name + '') TO ['' + grantee + '']'' +
CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTEE <> ''public''
AND grantee = ''[' + @User + ']'''
INSERT INTO #TempSecurables2
EXEC master..sp_executesql @SCDB
END
ELSE
BEGIN
--Server level Privileges to User or User Group
INSERT INTO #TempSecurables
SELECT CASE CAST(p.state AS VARCHAR(100)) WHEN 'D' THEN 'DENY' WHEN 'R' THEN 'REVOKE' WHEN 'G' THEN 'GRANT' WHEN 'W' THEN 'GRANT' END,
CASE CAST(p.state AS VARCHAR(100)) WHEN 'W' THEN 'WITH GRANT OPTION' ELSE '' END, CAST(p.permission_name AS VARCHAR(100)), RTRIM(p.class_desc),
(SELECT [name] FROM sys.server_principals WHERE principal_id = p.grantor_principal_id), CAST(l.name AS VARCHAR(100))
FROM sys.server_permissions p JOIN sys.server_principals l
ON p.grantee_principal_id = l.principal_id
AND l.is_disabled = 0
AND l.type IN ('S', 'U', 'G', 'R')
AND QUOTENAME(l.name) = QUOTENAME(@User)
SET @SCUser = 'USE [?]
--Privileges for Procedures/Functions/CLR/Views to the User
SELECT ''[?]'', CASE WHEN (b.state_desc COLLATE database_default) = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE (b.state_desc COLLATE database_default) END + '' EXECUTE ON ['' + c.name + ''].['' + a.name + ''] TO '' + QUOTENAME(USER_NAME(b.grantee_principal_id)) +
CASE STATE WHEN ''W'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM sys.all_objects a, sys.database_permissions b, sys.schemas c
WHERE a.OBJECT_ID = b.major_id AND a.type IN (''X'',''P'',''FN'',''AF'',''FS'',''FT'') AND b.grantee_principal_id <>0
AND b.grantee_principal_id <>2 AND a.schema_id = c.schema_id
AND QUOTENAME(USER_NAME(b.grantee_principal_id)) = ''[' + @User + ']''
ORDER BY c.name
--Table and View Level Privileges to the User
SELECT ''[?]'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] TO ['' + grantee + '']'' +
CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE <> ''public''
AND grantee = ''[' + @User + ']''
--Column Level Privileges to the User
SELECT ''[?]'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] ('' + column_name + '') TO ['' + grantee + '']'' +
CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION''
ELSE '''' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
WHERE GRANTEE <> ''public''
AND grantee = ''[' + @User + ']'''
INSERT INTO #TempSecurables2
EXEC master.dbo.sp_MSforeachdb @command1=@SCUser
END
DECLARE @tmpstr NVARCHAR(128)
SET @tmpstr = '** Generated ' + CONVERT (VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT CHAR(13) + '--##### Server level Privileges to User or User Group #####' + CHAR(13)
DECLARE cSC CURSOR FAST_FORWARD FOR SELECT 'USE [master];' + CHAR(10) + RTRIM(ts.[State]) + ' ' + RTRIM(ts.[PermName]) + ' TO ' + QUOTENAME(RTRIM(ts.[User])) + ' ' + RTRIM(ts.[State2]) + ';' + CHAR(10) + 'GO' FROM #TempSecurables ts WHERE RTRIM([Type]) = 'SERVER'
OPEN cSC
FETCH NEXT FROM cSC INTO @SC
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SC
FETCH NEXT FROM cSC INTO @SC
END
CLOSE cSC
DEALLOCATE cSC
DECLARE cSC CURSOR FAST_FORWARD FOR SELECT 'USE [master];' + CHAR(10) + RTRIM(ts.[State]) + ' ' + RTRIM(ts.[PermName]) + ' ON ' + CASE WHEN RTRIM(ts.[Type]) = 'SERVER_PRINCIPAL' THEN 'LOGIN' ELSE 'ENDPOINT' END + '::' + QUOTENAME(RTRIM(ts.[Grantor])) + ' TO ' + QUOTENAME(RTRIM(ts.[User])) + ' ' +RTRIM(ts.[State2]) + ';' + CHAR(10) + 'GO' FROM #TempSecurables ts WHERE RTRIM([Type]) <> 'SERVER'
OPEN cSC
FETCH NEXT FROM cSC INTO @SC
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SC
FETCH NEXT FROM cSC INTO @SC
END
CLOSE cSC
DEALLOCATE cSC
DROP TABLE #TempSecurables
PRINT CHAR(13) + '--##### Procedures/Functions/CLR/Views, Table and Column Level Privileges to the User #####' + CHAR(13)
DECLARE cSC CURSOR FAST_FORWARD FOR SELECT 'USE ' + ts2.DBName +';' + CHAR(10) + RTRIM(ts2.[State]) + ';' + CHAR(10) + 'GO' FROM #TempSecurables2 ts2
OPEN cSC
FETCH NEXT FROM cSC INTO @SC
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SC
FETCH NEXT FROM cSC INTO @SC
END
CLOSE cSC
DEALLOCATE cSC
DROP TABLE #TempSecurables2
GO
This script does not handle database roles memberships –
Syphilology
Back then I've created this small script to query permissions in our server. It was the first time I could make use of SQL cursors.. :) Hope this helps:
--cursor drop table #permission_report --create #temp table CREATE TABLE #permission_report (db_name varchar(50), username varchar(50), objectname varchar(100), objectclass varchar(50), permission_name varchar(50), state varchar(50))declare @dbname VARCHAR(50)
--declare cursor in order to run on every database on the server DECLARE c_dbnames CURSOR FOR SELECT name FROM sys.databases
OPEN c_dbnames
FETCH c_dbnames INTO @dbname WHILE @@Fetch_Status = 0 BEGIN --Openrowset to select the appropriate columns from system catalog views --insert result into #temp table --repeat task on every database on server EXEC('INSERT INTO #permission_report(db_name,username,objectname,objectclass,permission_name,state)
SELECT '''+@dbname+''',p.name username, o.name objectname, class_desc,permission_name, state_desc FROM ' + @dbname +'.sys.database_principals p JOIN ' + @dbname +'.sys.database_permissions d ON d.grantee_principal_id = p.principal_id JOIN ' + @dbname +'.sys.objects o ON o.object_id = d.major_id where p.name=''Yourdomain\User''') ---checking only EMRSN\USMTN-FF20_Users FETCH c_dbnames INTO @dbname END CLOSE c_dbnames DEALLOCATE c_dbnames SELECT * FROM #permission_report
You can modify the where clause of the object selection as you see fit. Should be minimal tweeking if you are looking for a 2005 or 2008 script.
/Caution!/ This script can be a little dangerous.
Declare @TableName varchar(100),
@Sql nvarchar(500),
@Result int,
@UserName nvarchar(258)
set @UserName= QuoteName('<your_user>')
Print @UserName
DECLARE
Your_Cursor cursor
LOCAL
FORWARD_ONLY
OPTIMISTIC
FOR
/* if you only want one object to apply permissions to*/
-- select Name from Sysobjects where name = 'Your_TableName'
/*tables*/-- select name from sysobjects where xtype = 'U' order by name
/*views*/-- select name from sysobjects where xtype = 'V' order by name
/*StoredPs*/-- select name from sysobjects where xtype = 'P' order by name
/*UDFs*/-- select name from sysobjects where xtype = 'FN' order by name
/**********************************************************************/
OPEN Your_Cursor
FETCH NEXT from Your_Cursor into @TableName
while (@@fetch_status = 0)
begin
/*Tables*/
-- set @Sql = N'Grant Select On '+ @TableName+ N' To ' + @UserName
-- set exec @Result = sp_executeSql @Sql
-- if @Result = 0
-- begin
-- Print 'Granted Select On '+ @TableName + ' by ' + @UserName
-- end
-- set @Sql = N'Grant Insert On '+ @TableName+ N' To ' + @UserName
-- set exec @Result = sp_executeSql @Sql
-- if @Result = 0
-- begin
-- Print 'Granted Insert On '+ @TableName + ' by ' + @UserName
-- end
-- set @Sql = N'Grant Update On '+ @TableName+ N' To '+ @UserName
-- set exec @Result = sp_executeSql @Sql
-- if @Result = 0
-- begin
-- Print 'Granted Update On '+ @TableName + ' by ' + @UserName
-- end
-- set @Sql = N'Grant Delete On '+ @TableName+ N' To '+ @UserName
-- set exec @Result = sp_executeSql @Sql
-- if @Result = 0
-- begin
-- Print 'Granted Delete On '+ @TableName + ' by ' + @UserName
-- end
/*Stored Procs and UDFs*/
-- set @Sql = N'Grant Execute On '+ @TableName+ N' To '+ @UserName
-- set exec @Result = sp_executeSql @Sql
-- if @Result = 0
-- begin
-- Print 'Granted Execute On '+ @TableName + ' by ' + @UserName
-- end
FETCH NEXT from your_Cursor into @TableName
end
CLOSE Your_Cursor
DEALLOCATE Your_Cursor
I've updated script I found here https://www.databasejournal.com/ms-sql/re-generating-sql-server-logins-part-2/ for newer version of MS SQL server - it generates following for example:
-- Create database user for login
USE [master]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'YourLoginName')
BEGIN CREATE USER [YourLoginName] FOR LOGIN [YourLoginName] WITH DEFAULT_SCHEMA=[dbo] END GO
--Grant all securable class to login
USE [master] GRANT CONNECT TO [YourLoginName] GO
----------------
--Login Pre-requisites
----------------
set concat_null_yields_null off
USE master
go
SET NOCOUNT ON
DECLARE @login_name varchar(100)
SET @login_name = 'YourSQLloginName'
---------------------–
IF lower(@login_name) IN ('sa','public')
BEGIN
RAISERROR (15405,11,1,@login_name)
RETURN
END
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = @login_name AND type IN ('G','U','S'))
BEGIN
PRINT 'Please input valid login name'
RETURN
END
DECLARE @login_sid varbinary(85)
SELECT @login_sid = sid FROM sys.server_principals
WHERE name = @login_name
DECLARE @maxid int
IF OBJECT_ID('tempdb..#db_users') is not null
BEGIN
DROP TABLE #db_users
END
SELECT id = identity(int,1,1), sql_cmd = 'SELECT '''+name+''', * FROM ['+name+'].sys.database_principals' INTO #db_users FROM sys.databases
WHERE state_desc <> 'OFFLINE'
SELECT @maxid = @@ROWCOUNT
----------------
--–Create Server Role Temp table
----------------
IF OBJECT_ID('tempdb..#srvrole') IS NOT NULL
BEGIN
DROP TABLE #srvrole
END
CREATE TABLE #srvrole(ServerRole sysname, MemberName sysname, MemberSID varbinary(85))
INSERT INTO [#srvrole] EXEC sp_helpsrvrolemember
DECLARE @login_srvrole varchar(1000)
SET @login_srvrole = ''
IF EXISTS (select * from [#srvrole] where ServerRole = 'sysadmin' AND MemberName = @login_name)
BEGIN
PRINT '–Login ['+@login_name+'] is part of sysadmin server role, hence possesses full
privileges for SQL instance: '+@@servername
PRINT 'GO'
SELECT @login_srvrole =
@login_srvrole + 'EXEC
sp_addsrvrolemember '''+MemberName+''','''+ServerRole+''''+CHAR(10) FROM #srvrole
WHERE [MemberName] = @login_name
PRINT @login_srvrole
RETURN
RETURN
END
-----------------
--Find out list of db that the
--login has access to
-----------------
PRINT ''
PRINT '----------------'
PRINT '–Create database user for login '
PRINT '----------------'
IF OBJECT_ID('tempdb..#alldb_users') is not null
BEGIN
DROP TABLE #alldb_users
END
CREATE TABLE #alldb_users(
[dbname][sysname] NOT NULL,
[name][sysname] NOT NULL,
[principal_id][int] NOT NULL,
[type][char](1) NOT NULL,
[type_desc][nvarchar](60) NULL,
[default_schema_name][sysname] NULL,
[create_date][datetime] NOT NULL,
[modify_date][datetime] NOT NULL,
[owning_principal_id][int] NULL,
[sid][varbinary](85)NULL,
[is_fixed_role][bit] NOT NULL,
[authentication_type][int] NOT NULL,
[authentication_type_desc][nvarchar](100) NOT NULL,
[default_language_name][sysname] NULL,
[default_language_lcid][int] NULL,
[allow_encrypted_value_modifications][bit] NOT NULL
)
DECLARE @id int, @sqlcmd varchar(500)
SET @id = 1
WHILE @id <=@maxid
BEGIN
SELECT @sqlcmd = sql_cmd FROM #db_users WHERE id = @id
INSERT INTO #alldb_users EXEC (@sqlcmd)
SET @id = @id + 1
END
DELETE FROM #alldb_users WHERE sid is null
DELETE FROM #alldb_users WHERE sid <> @login_sid
IF NOT EXISTS (SELECT * FROM #alldb_users )
BEGIN
PRINT '–Login ['+@login_name+'] doesnt have access to any database'
END
DECLARE @name sysname, @dbname sysname, @schema sysname, @dbuser_cmd varchar(8000)
DECLARE dbuser_cursor CURSOR
FAST_FORWARD FOR
SELECT dbname, name, default_schema_name
FROM #alldb_users
OPEN dbuser_cursor
FETCH NEXT FROM dbuser_cursor INTO @dbname, @name, @schema
WHILE @@FETCH_STATUS = 0
BEGIN
IF @schema IS NOT NULL
BEGIN
SELECT @dbuser_cmd = 'USE ['+dbname+']
IF NOT EXISTS (SELECT * FROM
sys.database_principals WHERE name = '''+name+''')
BEGIN
CREATE
USER ['+@name+'] FOR LOGIN ['+@login_name+']'+isnull(' WITH DEFAULT_SCHEMA=['+default_schema_name+']','')+'
END
GO
' FROM #alldb_users WHERE name = @name and dbname = @dbname
END
ELSE
BEGIN
SELECT @dbuser_cmd = 'USE ['+dbname+']
IF NOT EXISTS (SELECT * FROM
sys.database_principals WHERE name = '''+name+''')
BEGIN
CREATE
USER ['+@name+'] FOR LOGIN ['+@login_name+']
END
GO
' FROM #alldb_users WHERE name = @name and dbname = @dbname
END
print @dbuser_cmd
FETCH NEXT FROM dbuser_cursor INTO
@dbname, @name,
@schema
END
CLOSE dbuser_cursor
DEALLOCATE dbuser_cursor
----------------
--Create DB Role Temp table
----------------
IF OBJECT_ID('tempdb..#dbrole') is not null
BEGIN
DROP TABLE #dbrole
END
create table #dbrole (id int identity(1,1), dbname varchar(100), dbrole varchar (100), dbrole_member varchar(100), sid varbinary(85),
default_schema_name varchar(100), login_name varchar(100), db_principal_id int)
DECLARE @dbrole_sqlcmd varchar(max)
DECLARE dbrole_cursor CURSOR
FAST_FORWARD FOR
SELECT
'SELECT '''+dbname+''', c.name, b.name, b.sid, b.default_schema_name, d.name,
b.principal_id as login_name
from ['+dbname+'].sys.database_role_members a
inner join ['+dbname+'].sys.database_principals b on a.member_principal_id = b.principal_id
inner join ['+dbname+'].sys.database_principals c on a.role_principal_id = c.principal_id
left join sys.server_principals d on b.sid = d.sid
where d.name= '''+@login_name+''''
from #alldb_users
OPEN dbrole_cursor
FETCH NEXT FROM dbrole_cursor INTO @dbrole_sqlcmd
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #dbrole (dbname, dbrole,
dbrole_member, sid, default_schema_name,
login_name, db_principal_id) exec(@dbrole_sqlcmd)
FETCH NEXT FROM dbrole_cursor INTO @dbrole_sqlcmd
END
CLOSE dbrole_cursor
DEALLOCATE dbrole_cursor
DELETE FROM #dbrole WHERE sid <> @login_sid
IF EXISTS (SELECT * FROM #dbrole where
dbrole = 'db_owner')
BEGIN
PRINT '----------------'
PRINT'–Login is db_owner of below databases'
PRINT'----------------'
END
DECLARE @dbname_dbowner varchar(100), @dbrole_member varchar(100)
DECLARE dbowner_cursor CURSOR
FAST_FORWARD FOR
SELECT dbname, dbrole_member from #dbrole where
dbrole = 'db_owner'
OPEN dbowner_cursor
FETCH NEXT FROM dbowner_cursor INTO
@dbname_dbowner, @dbrole_member
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'USE ['+@dbname_dbowner+']
EXEC sp_addrolemember ''db_owner'','''+@dbrole_member +'''
GO'
FETCH NEXT FROM dbowner_cursor INTO @dbname_dbowner, @dbrole_member
END
CLOSE dbowner_cursor
DEALLOCATE dbowner_cursor
----------------------------------–
--–-Find out what database the login
--has permission to access (avoid restricted and single user database)
----------------------------------–
DELETE From #srvrole where MemberName <>
@login_name
IF OBJECT_ID('tempdb..#alldb_users_access') IS NOT NULL
BEGIN
DROP TABLE #alldb_users_access
END
SELECT a.*, collation_name INTO #alldb_users_access FROM
#alldb_users a inner join
sys.databases b ON a.dbname = b.name
WHERE user_access = 0
OR
(user_access = 2 and exists (SELECT * FROM #srvrole WHERE
ServerRole in ('dbcreator','sysadmin')))
OR
(user_access = 2 and a.dbname in (SELECT dbname FROM #dbrole WHERE dbrole = 'db_owner'
AND login_name = @login_name))
----------------------------------–
--Remove database that login doesnt
--have permission to connect
----------------------------------–
IF OBJECT_ID('tempdb..#dbconnect') is not null
BEGIN
DROP TABLE #dbconnect
END
CREATE TABLE #dbconnect ( dbname varchar(100), connect_status bit)
DECLARE @dbconnect_sqlcmd varchar(1000)
SET @dbconnect_sqlcmd = ''
DECLARE dbbconnect_cursor CURSOR
FAST_FORWARD FOR
SELECT 'select distinct '''+dbname+''', 1 from ['+dbname+'].sys.database_permissions a
inner join ['+dbname+'].sys.database_principals b on a.grantee_principal_id = b.principal_id
inner join ['+dbname+'].sys.server_principals c on b.sid = c.sid
where c.name = '''+@login_name+''''
from #alldb_users_access
OPEN dbbconnect_cursor
FETCH NEXT FROM dbbconnect_cursor INTO @dbconnect_sqlcmd
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #dbconnect exec(@dbconnect_sqlcmd)
FETCH NEXT FROM dbbconnect_cursor INTO @dbconnect_sqlcmd
END
CLOSE dbbconnect_cursor
DEALLOCATE dbbconnect_cursor
insert into #dbconnect
select a.dbname, 0 from #alldb_users_access a
left join #dbconnect b on a.dbname = b.dbname
where b.dbname is null
-----------------------------
--Grant
--all securable class to login
------------------------------
PRINT ''
PRINT '----------------'
PRINT '–Grant all securable class to login '
PRINT '----------------'
IF OBJECT_ID('tempdb..#securable_class') is not null
BEGIN
DROP TABLE #securable_class
END
IF OBJECT_ID('tempdb..#dblevel') is not null
BEGIN
DROP TABLE #dblevel
END
create table #dblevel (login_name varchar(256), dbname sysname, dbuser_name varchar(100), class_desc varchar(100), permission_name varchar(100), state_desc varchar(100))
DECLARE @dblevel_sqlcmd varchar(1000)
DECLARE dblevel_cursor CURSOR
FAST_FORWARD FOR
SELECT 'select '''+@login_name+''' as login_name, '''+dbname+''' as dbname, b.name as dbuser_name, a.class_desc, a.permission_name, state_desc
from ['+dbname+'].sys.database_permissions a
inner join ['+dbname+'].sys.database_principals b
on a.grantee_principal_id = b.principal_id
where b.name in (''public'','''+name+''')
and class_desc = ''DATABASE'''
FROM #alldb_users_access
union
SELECT 'select '''+@login_name+''' as login_name, ''master'' as dbname, b.name as dbuser_name, a.class_desc, a.permission_name, state_desc
from sys.server_permissions a
inner join sys.server_principals b on a.grantee_principal_id = b.principal_id
where b.name = '''+@login_name+''''
UNION
SELECT 'select '''+@login_name+''' as login_name, ''master'' as dbname, b.name as dbuser_name, a.class_desc, a.permission_name, state_desc
from sys.server_permissions a
inner join sys.server_principals b on a.grantee_principal_id = b.principal_id and class_desc = ''SERVER''
where b.name = ''public'''
OPEN dblevel_cursor
FETCH NEXT FROM dblevel_cursor INTO
@dblevel_sqlcmd
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #dblevel (login_name, dbname, dbuser_name, class_desc, permission_name, state_desc)
EXEC (@dblevel_sqlcmd)
FETCH NEXT FROM dblevel_cursor INTO @dblevel_sqlcmd
END
CLOSE dblevel_cursor
DEALLOCATE dblevel_cursor
SET NOCOUNT ON
DELETE FROM #dblevel WHERE permission_name IN ('SELECT','INSERT','UPDATE','DELETE','REFERENCES')
DELETE FROM #dblevel WHERE dbname IN (SELECT dbname FROM #dbrole
WHERE sid = @login_sid AND dbrole = 'db_owner')
DECLARE @securable_sqlcmd varchar(150)
DECLARE securable_cursor CURSOR
FAST_FORWARD FOR
SELECT distinct 'USE ['+dbname+']
GRANT '+permission_name+' TO ['+@login_name+']
GO
' FROM #dblevel
OPEN securable_cursor
FETCH NEXT FROM securable_cursor INTO @securable_sqlcmd
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @securable_sqlcmd
FETCH NEXT FROM securable_cursor INTO @securable_sqlcmd
END
CLOSE securable_cursor
DEALLOCATE securable_cursor
Here is a query to generate commands to apply incurables permissions for Tables, Procedures and Views in SQL Server for a specific database:
Use [YourDatabaseName]
Go
SELECT state_desc + ' ' + permission_name + ' ON [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME(major_id) + '] TO [' + @User + ']'
FROM sys.database_permissions dp
JOIN sys.database_principals dbp ON dp.grantee_principal_id = dbp.principal_id
JOIN
(
SELECT OBJECT_ID,schema_id FROM sys.tables
UNION ALL
SELECT OBJECT_ID,schema_id FROM sys.views
UNION ALL
SELECT OBJECT_ID,schema_id from sys.procedures
) t on t.object_id = dp.major_id
WHERE USER_NAME(grantee_principal_id) = 'YourUserName'
© 2022 - 2025 — McMap. All rights reserved.