September 14, 2017 at 4:02 am
Hi all
Help please!
Why does it seem so complicated to script out schema permissions in SQL? I've got various scripts that script out roles permissions, database permissions etc. but none of these script out schema permissions. I've googled, tried various scripts and none seem to be working, just get blank results for my schema. Maybe SQL 2016 works differently. Does anyone have a SQL 2016 friendly script that will script out all the permissions for a schema (input would be schema name, user as well though not required).
Thanks in advance 🙂
September 14, 2017 at 4:52 am
Works just the same, sys.database_permissions join to sys.database_principals where the permission class is 3
select perm.state_desc + ' ' + perm.permission_name + ' ON SCHEMA::['+SCHEMA_NAME(major_id)+'] TO ['+users.name collate database_default+'] as [dbo];'
from sys.database_permissions perm
inner join sys.database_principals users
on perm.grantee_principal_id = users.principal_id
where perm.class = 3
September 14, 2017 at 6:30 am
Thanks Anthony... i now realise that some of the previous scripts I was using wwere actually working - i was getting no results because they were no explicit permissions, though i was certain i had added permissions but obviously hadn't.
Thanks again!
September 14, 2017 at 7:17 am
doodlingdba - Thursday, September 14, 2017 4:02 AMHi allHelp please!
Why does it seem so complicated to script out schema permissions in SQL? I've got various scripts that script out roles permissions, database permissions etc. but none of these script out schema permissions. I've googled, tried various scripts and none seem to be working, just get blank results for my schema. Maybe SQL 2016 works differently. Does anyone have a SQL 2016 friendly script that will script out all the permissions for a schema (input would be schema name, user as well though not required).
Thanks in advance 🙂
Try this
--====================================================================================
--====================================================================================
--====== Script author: Perry Whittle ==
--====== Script date: 25th Jan 2015 ==
--====== Script task: This script will dump any database pertinent info ==
--====== This includes user permissions, role membership, etc. ==
--====== If the database is TDE protected it will also generate ==
--====== a backup script for the cert and a create script for ==
--====== the cert from the same backup. ==
--====== ==
--====== ==
--====== This script is provided without warranty and is ==
--====== developed to aid in database permissions analysis ==
--====== and recreation. The developer takes no responsibility ==
--====== for those who choose to execute this script without ==
--====== first reading and understanding what the script does. ==
--====== It is not designed to be invasive, but it is still a ==
--====== script which can be modified at any point to deviate ==
--====== from it's original intentions. The output from this ==
--====== script, does however make changes to your SQL Server ==
--====== system if you choose to execute it, use caution!! ==
--====================================================================================
--====================================================================================
--====================================================================================
--====== Declare any variables and set their values ==
--====================================================================================
SET NOCOUNT ON
DECLARE @sql NVARCHAR(MAX)
DECLARE @rundate VARCHAR(25)
SET @sql = ''
SET @rundate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 120), ' ', '_'), ':', ''), '-', '')
SELECT @sql =
'/*====================================================================================' + CHAR(10) +
'==== IMPORTANT: Before executing these scripts check the details to ensure they ====' + CHAR(10) +
'==== are valid. For instance when crossing domains ====' + CHAR(10) +
'====================================================================================*/' + CHAR(10)
PRINT @sql
SET @sql = ''
--=======================================================
--Check the database encrytion state
--=======================================================
IF (SELECT count(*) FROM sys.databases WHERE database_id = DB_ID() AND is_encrypted = 1) = 0
BEGIN
SELECT @sql = '/*Database ' + QUOTENAME(DB_NAME(DB_ID())) + ' is not TDE protected*/'
END
ELSE
BEGIN
SELECT @sql =
CASE
WHEN encryption_state <> 0 THEN '/*Database ' + QUOTENAME(DB_NAME(DB_ID())) +
' is TDE protected, to ensure you have a backup of the' + CHAR(13) +
' certificate that the database is protected with, including the' + CHAR(13) +
' certificates private key passsword, use the following scripts.' + CHAR(13) + CHAR(13) +
'Important: You must create a master key on your new instance first, do this now' + CHAR(13) +
'using the script below, then use the scripts that follow to implement TDE.' + CHAR(10) +
'===================================================================================' + CHAR(13) +
'*!Don''t forget to change the password before executing!*/' + CHAR(13) +
'CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''D@nger W1ll Rob1nson''' + CHAR(13)
WHEN encryption_state = 0 THEN @sql + CHAR(13)
END + CHAR(13) +
'/*The database [' + DB_NAME() + '] is protected by certificate [' +
c.name + '] in the master database.' + CHAR(13) +
'Use the following script to backup and restore the certificate.' +
' Ensure you change the drive, path and passwords*/' + CHAR(13) + CHAR(13) +
'--Create the backup command first ....' + CHAR(13) +
'USE [master]' + CHAR(13) + 'GO' + CHAR(13) +
'BACKUP CERTIFICATE [' + c.name + '] TO FILE = ''?:\!!!!!!!!\!!!!!!!\' + c.name + '_' + @rundate + '.cer''' + CHAR(13) +
'WITH PRIVATE KEY (FILE = ''?:\!!!!!!!!\!!!!!!!!\' + c.name + '_' + @rundate + '.pky'',' + CHAR(13) +
'ENCRYPTION BY PASSWORD = ''P@ssw0rdt0encryptcertbackup'')' + CHAR(13) + CHAR(13) +
'--Now the Create command from file ....' + CHAR(13) +
'USE [master]' + CHAR(13) + 'GO' + CHAR(13) +
'CREATE CERTIFICATE [' + c.name + '] FROM FILE = ''?:!!!!!!\!!!!!!\' + c.name + '_' + @rundate + '.cer''' + CHAR(13) +
'WITH PRIVATE KEY (FILE = ''?:\!!!!!!!!\!!!!!!!!\' + c.name + '_' + @rundate + '.pky'',' + CHAR(13) +
'DECRYPTION BY PASSWORD = ''P@ssw0rdt0encryptcertbackup'')' + CHAR(13) + CHAR(13) +
'--Set the database encryption key ....' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'GO' + CHAR(13) +
'CREATE DATABASE ENCRYPTION KEY' + CHAR(13) +
'WITH ALGORITHM = AES_256' + CHAR(13) +
'ENCRYPTION BY SERVER CERTIFICATE [' + c.name + ']' + CHAR(13) +
'GO' + CHAR(13) + CHAR(10) + CHAR(13) +
'--Turn on encryption for the database' + CHAR(13) +
'ALTER DATABASE [TDE_Database] SET ENCRYPTION ON' + CHAR(13) +
'GO' + CHAR(13)
FROM master.sys.dm_database_encryption_keys dek
INNER JOIN master.sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint
WHERE dek.database_id = DB_ID()
END
PRINT @sql + CHAR(13) + CHAR(13)
SET @sql = ''
--=======================================================
--Who owns the database?
--=======================================================
SELECT @sql = 'The server login [' + sp.name + '] owns the database [' + d.name + ']'
FROM sys.databases d
INNER JOIN sys.server_principals sp
ON d.owner_sid = sp.sid
WHERE d.database_id = DB_ID()
PRINT @sql + CHAR(13) + CHAR(13)
SET @sql = ''
--========================================================
--script any certificates in the database
--========================================================
IF (SELECT COUNT(*) FROM sys.certificates WHERE name NOT LIKE '[#][#][M]%##') = 0
BEGIN
SELECT @sql = @sql + '/*No certificates found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Creating backup scripts for all user certificates' + CHAR(10) +
'===================================================================================*/' + CHAR(13) + CHAR(13)
SELECT @sql = @sql + '!!! Important: Replace the drive letter, path info and encryption password below !!!' + CHAR(13)
SELECT @sql = @sql + '--Create the backup command first ....' + CHAR(13)
SELECT @sql = @sql + 'USE [master]' + CHAR(13) + 'GO' + CHAR(13)
SELECT @sql = @sql + 'BACKUP CERTIFICATE [' + name +
'] TO
FILE = ''?:\!!!!!!!!\!!!!!!!\' + name + '.cer''
WITH PRIVATE KEY(
FILE = ''?:\!!!!!!!!\!!!!!!!!\' + name + '.pky'',
ENCRYPTION BY PASSWORD = ''P@ssw0rdt0encryptcertbackup'')' + CHAR(13) + CHAR(10) + CHAR(13) +
'--Now the Create command from file....' + CHAR(13) +
'CREATE CERTIFICATE [' + name + ']' +
'FROM FILE = ''?:!!!!!!\!!!!!!\' + name + '.cer''
WITH PRIVATE KEY(
FILE = ''?:\!!!!!!!!\!!!!!!!!\' + name + '.pky'',
DECRYPTION BY PASSWORD = ''P@ssw0rdt0encryptcertbackup'')' + CHAR(13)
FROM sys.certificates
WHERE name NOT LIKE '[#][#][M]%[#][#]'
PRINT @sql + CHAR(13) + CHAR(13)
END
SET @sql = ''
--======================================================
--Script the database users
--======================================================
SELECT principal_id INTO #users FROM sys.database_principals WHERE type IN ('U', 'G', 'S') AND principal_id > 4
IF (SELECT COUNT(*) FROM #users) = 0
BEGIN
SELECT @sql = @sql + '/*No database users found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @uid INT
SELECT @sql = '/*Scripting all database users and schemas' + CHAR(10) +
'===================================================================================' + CHAR(13) +
'Note: these are the users found in the database, but they may not all be valid, check them first*/' +
CHAR(13) + CHAR(13)
WHILE (SELECT TOP 1 principal_id FROM #users) IS NOT NULL
BEGIN
SELECT TOP 1 @uid = principal_id FROM #users
SELECT @sql = @sql + 'IF (SELECT name FROM sys.database_principals WHERE name = ''' +
dp.name + ''') IS NULL' + CHAR(13) + 'BEGIN' + CHAR(13) +
'CREATE USER ' + QUOTENAME(dp.name) +
CASE
WHEN SUSER_SNAME(dp.sid) IS NULL THEN ' WITHOUT LOGIN'
ELSE ' FOR LOGIN ' + QUOTENAME(SUSER_SNAME(dp.sid))
END +
CASE
WHEN dp.type <> 'G' AND dp.default_schema_name IS NULL THEN ' WITH DEFAULT_SCHEMA = [dbo]'
WHEN dp.type IN ('G', 'C', 'K') THEN ''
ELSE ' WITH DEFAULT_SCHEMA = [' + dp.default_schema_name + ']'
END + CHAR(13) + 'END'
FROM sys.database_principals dp LEFT OUTER JOIN
sys.schemas sch ON dp.principal_id = sch.principal_id
WHERE dp.principal_id = @uid AND dp.[type] IN ('U', 'G', 'S') AND dp.principal_id > 4
AND dp.name NOT LIKE '[#][#][M]%[#][#]'
PRINT @sql + CHAR(10)
DELETE FROM #users WHERE principal_id = @uid
SELECT @sql = ''
END
END
DROP TABLE #users
SELECT @sql = ''
--========================================================
--Script any users that are protected by a cert
--========================================================
IF (SELECT count(*) FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid
WHERE dp.type = 'C' AND dp.principal_id > 4) = 0
BEGIN
SELECT @sql = @sql + '/*No certificated users found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all certificated database users' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
--SELECT @sql = @sql + 'USE ' + QUOTENAME(DB_NAME(DB_ID())) + CHAR(13) --+ 'GO' + CHAR(10)
SELECT @sql = @sql + 'CREATE USER ' + QUOTENAME(dp.name) + ' FOR CERTIFICATE ' + c.name
FROM sys.database_principals dp INNER JOIN sys.certificates c ON dp.sid = c.sid
WHERE dp.type = 'C' AND dp.principal_id > 4 AND dp.name NOT LIKE '[#][#][M]%[#][#]'
PRINT @sql + CHAR(13) + CHAR(13)
END
SET @sql = ''
--========================================================
--script custom database roles from the database
--========================================================
IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'R' AND is_fixed_role <> 1 AND principal_id > 4) = 0
BEGIN
SELECT @sql = @sql + '/*No custom database roles found*/'
PRINT @sql + CHAR(13) + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all custom database roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + 'CREATE ROLE ' + QUOTENAME(dp.name) + ' AUTHORIZATION ' + QUOTENAME(dp2.name) + CHAR(13)
FROM sys.database_principals dp INNER JOIN sys.database_principals dp2
ON dp.owning_principal_id = dp2.principal_id
WHERE dp.type = 'R' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4
PRINT @sql + CHAR(13) + CHAR(13)
END
SET @sql = ''
--=======================================================
--script all schema permissions
--=======================================================
IF (SELECT COUNT(*) FROM sys.database_permissions dp
INNER JOIN sys.schemas sch ON dp.grantor_principal_id = sch.principal_id
INNER JOIN sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id
INNER JOIN sys.database_principals dp3 ON dp.grantor_principal_id = dp3.principal_id
WHERE dp.class = 3) = 0
BEGIN
SELECT @sql = @sql + '/*No schema permissions found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
DECLARE @GDR VARCHAR(10), @Permission VARCHAR(32), @PermClass VARCHAR(24)
DECLARE @SrcSch VARCHAR(128), @RoleName VARCHAR(128), @PrincName VARCHAR(128)
SELECT @sql = '/*Scripting all user schema permissions' + CHAR(10) +
'===================================================================================*/'
IF (OBJECT_ID('tempdb..#schperms', 'U') IS NOT NULL)
BEGIN
DROP TABLE #schperms
END
CREATE TABLE #schperms(
GDR VARCHAR(10) NULL,
Permission VARCHAR(32) NULL,
PermClass VARCHAR(24) NULL,
SrcSch VARCHAR(128) NULL,
RoleName VARCHAR(128) NULL,
PrincName VARCHAR(128) NULL
)
INSERT INTO #schperms
SELECT dp.state_desc,
dp.permission_name,
dp.class_desc,
sch.name,
dp2.name,
dp3.name
FROM sys.database_permissions dp
INNER JOIN sys.schemas sch ON dp.grantor_principal_id = sch.principal_id
INNER JOIN sys.database_principals dp2 ON dp.grantee_principal_id = dp2.principal_id
INNER JOIN sys.database_principals dp3 ON dp.grantor_principal_id = dp3.principal_id
WHERE dp.class = 3
PRINT @sql
WHILE (SELECT COUNT(*) FROM #schperms) > 0
BEGIN
--Script the permission grants on the schemas
SELECT TOP 1 @GDR = GDR, @Permission = permission, @PermClass = permclass,
@SrcSch = srcsch, @RoleName = rolename, @PrincName = princname
FROM #schperms
SELECT @sql = CHAR(13) + @GDR COLLATE latin1_general_ci_as + ' ' +
@Permission + ' ON ' + @Permclass + '::' + QUOTENAME(@SrcSch) +
' TO ' + QUOTENAME(@Rolename) + ' AS ' + QUOTENAME(@Princname)
PRINT @sql
DELETE FROM #schperms WHERE GDR=@GDR AND Permission=@Permission AND @PermClass=PermClass AND
@SrcSch=SrcSch AND @RoleName=RoleName AND PrincName=@PrincName
END
DROP TABLE #schperms
END
SET @sql = ''
--=========================================================
--script Application roles from the database
--=========================================================
IF (SELECT COUNT(*) FROM sys.database_principals WHERE type = 'A') = 0
BEGIN
SELECT @sql = @sql + CHAR(13) + '/*No application roles found*/'
PRINT @sql + CHAR(13) + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all application roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + 'CREATE APPLICATION ROLE ' + dp.name + ' WITH DEFAULT_SCHEMA = ' +
QUOTENAME(dp.default_schema_name) + ', PASSWORD = N''P@ssw0rd1''' + CHAR(10)
FROM sys.database_principals dp
WHERE dp.type = 'A' AND dp.is_fixed_role <> 1 AND dp.principal_id > 4
PRINT @sql + CHAR(13) + CHAR(13)
END
SET @sql = ''
--===============================================================
--got the roles so now we need to get any nested role permissions
--===============================================================
IF (SELECT COUNT(*) FROM sys.database_principals dp INNER JOIN sys.database_role_members drm
ON dp.principal_id = drm.member_principal_id INNER JOIN sys.database_principals dp2
ON drm.role_principal_id = dp2.principal_id WHERE dp.type = 'R') = 0
BEGIN
SELECT @sql = + '/*No nested roles found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all nested roles' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp2.name + ''', ''' + dp.name + '''' + CHAR(10)
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm
ON dp.principal_id = drm.member_principal_id
INNER JOIN sys.database_principals dp2
ON drm.role_principal_id = dp2.principal_id
WHERE dp.type = 'R'
PRINT @sql + CHAR(13) + CHAR(13)
END
SET @sql = ''
--================================================================
--Scripting all user connection grants
--================================================================
IF (SELECT COUNT(*) FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp
ON dpm.grantee_principal_id = dp.principal_id WHERE dp.principal_id > 4 AND dpm.class = 0 AND dpm.type = 'CO') = 0
BEGIN
SELECT @sql = + '/*No database connection GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN
SELECT @sql = '/*Scripting all database level permissions' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + dpm.state_desc COLLATE Latin1_General_CI_AS + ' ' +
dpm.permission_name COLLATE Latin1_General_CI_AS + ' TO ' + QUOTENAME(dp.name) + CHAR(13)
FROM sys.database_permissions dpm INNER JOIN sys.database_principals dp
ON dpm.grantee_principal_id = dp.principal_id
WHERE dp.principal_id > 4 AND dpm.class = 0 --AND dpm.type = 'CO'
AND dp.name NOT LIKE '[#][#][M]%[#][#]'
PRINT @sql + CHAR(13) + CHAR(13)
END
SET @sql = ''
--=================================================================
--Now all the object level permissions
--=================================================================
IF (SELECT COUNT(*) FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr
ON dbpr.principal_id = dbpe.grantee_principal_id INNER JOIN sys.objects obj
ON dbpe.major_id = obj.object_id WHERE obj.type NOT IN ('IT','S','X')) = 0
BEGIN
SELECT @sql = + '/*No database user object GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN SELECT @sql = '/*Scripting all database user object GRANTS' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
PRINT @sql
SET @sql = ''
IF OBJECT_ID('tempdb..#objgrants') IS NOT NULL
BEGIN
DROP TABLE #objgrants
END
CREATE TABLE #objgrants(
state_desc VARCHAR(60)
, perm_name NVARCHAR(128)
, sch_name NVARCHAR(128)
, maj_ID NVARCHAR(128)
, name NVARCHAR(128)
, pr_name NVARCHAR(128)
)
DECLARE @state_desc VARCHAR(60), @perm_name NVARCHAR(128), @sch_name NVARCHAR(128)
DECLARE @maj_ID NVARCHAR(128), @name NVARCHAR(128), @pr_name NVARCHAR(128)
INSERT INTO #objgrants
SELECT CASE dbpe.[state] WHEN 'W' THEN 'GRANT'
ELSE dbpe.state_desc COLLATE Latin1_General_CI_AS
END AS [state_desc]
, dbpe.permission_name COLLATE Latin1_General_CI_AS AS perm_name
, sch.name AS sch_name
, OBJECT_NAME(dbpe.major_id) AS maj_ID
, dbpr.name AS name
, CASE dbpe.[state] WHEN 'W' THEN '] WITH GRANT OPTION'
ELSE ']' END AS pr_name
FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr
ON dbpr.principal_id = dbpe.grantee_principal_id
INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE obj.type NOT IN ('IT','S','X')
ORDER BY dbpr.name, obj.name
WHILE (SELECT COUNT(*) FROM #objgrants) > 0
BEGIN
SELECT TOP 1 @state_desc = state_desc, @perm_name = perm_name, @sch_name = sch_name,
@maj_ID = maj_ID, @name = name, @pr_name = pr_name FROM #objgrants
SELECT @sql = @sql + @state_desc + ' ' + @perm_name +
' ON [' + @sch_name + '].[' + @maj_ID + '] TO [' + @name + @pr_name
PRINT @sql
SET @sql = ''
DELETE FROM #objgrants WHERE state_desc = @state_desc AND perm_name = @perm_name
AND sch_name = @sch_name AND maj_ID = @maj_ID AND name = @name AND pr_name = @pr_name
END
PRINT CHAR(13)
DROP TABLE #objgrants
END
SET @sql = ''
--=================================================================
--Now script all the database roles the user have permissions to
--=================================================================
IF (SELECT COUNT(*) FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R') = 0
BEGIN
SELECT @sql = + '/*No database user role GRANTS found*/'
PRINT @sql + CHAR(13) + CHAR(13)
END
ELSE
BEGIN SELECT @sql = '/*Scripting all database user role permissions' + CHAR(10) +
'===================================================================================*/' + CHAR(13)
SELECT @sql = @sql + 'EXEC sp_addrolemember ''' + dp.name + ''', ''' + dp2.name + '''' + CHAR(13)
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <> 'R'
PRINT @sql + CHAR(13) + CHAR(13)
ENDSET @sql = ''
SELECT @sql = '--Finished!' + CHAR(13) + '--Please ensure you check the script output' +
CHAR(13) + '--before executing against your target database.'
PRINT @sql
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 20, 2017 at 5:08 am
Thanks for sharing Perry - I am getting an error about incorrect syntax on line 305 of the script:
DELETE FROM #schperms WHERE GDR=@GDR AND ermission=@Permission">Permission=@Permission AND @PermClass=PermClass AND
This is how it has copied out the text from this post, obviously something not right...
September 20, 2017 at 5:18 am
doodlingdba - Wednesday, September 20, 2017 5:08 AMThanks for sharing Perry - I am getting an error about incorrect syntax on line 305 of the script:DELETE FROM #schperms WHERE GDR=@GDR AND ermission=@Permission">Permission=@Permission AND @PermClass=PermClass AND
This is how it has copied out the text from this post, obviously something not right...
the browser is doing something funny,
highlight the line in ssms by double clicking the red error and change the code to read
DELETE FROM #schperms WHERE GDR = @GDR AND Permission = @Permission AND @PermClass = PermClass AND @SrcSch = SrcSch AND @RoleName = RoleName AND PrincName = @Princname
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 20, 2017 at 5:33 am
Thanks - that's working nicely now! Thanks for sharing
September 26, 2017 at 3:17 am
doodlingdba - Wednesday, September 20, 2017 5:33 AMThanks - that's working nicely now! Thanks for sharing
😎😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply