Hi Folks,
Any one has a script to script out user permissions from Azure SQL database? I was not able to find this in any articles/forums.
Many thanks!
May 22, 2017 at 11:32 am
SQL!$@w$0ME - Thursday, January 26, 2017 11:20 AMHi Folks,
Any one has a script to script out user permissions from Azure SQL database? I was not able to find this in any articles/forums.
Many thanks!
Anyone has any scripts in hand to script out user permissions from azure db? Please share. Many thanks!
May 23, 2017 at 10:12 pm
I can't even spell Azure but, hopefully, this will act as a "bump" for your question so that someone might see it and answer. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2017 at 7:31 am
If you go to bottom of this post you will see some TSQL to run under the database in Azure. Its a starting point I think! It will give you the logins (AD ones too) with permissions assigned to them.
https://blobeater.blog/2017/05/22/ad-authentication-and-azure-sql-database/
thanks
June 1, 2017 at 9:16 am
BLOB EATER - Thursday, June 1, 2017 7:31 AMIf you go to bottom of this post you will see some TSQL to run under the database in Azure. Its a starting point I think! It will give you the logins (AD ones too) with permissions assigned to them.https://blobeater.blog/2017/05/22/ad-authentication-and-azure-sql-database/
thanks
Thanks, it would be good to have object level permissions scripted out.
August 30, 2017 at 12:00 pm
SQL!$@w$0ME - Thursday, January 26, 2017 11:20 AMHi Folks,
Any one has a script to script out user permissions from Azure SQL database? I was not able to find this in any articles/forums.
Many thanks!
Any one has a script handy to script out permissions from Azure SQL database?
Thanks!
August 30, 2017 at 4:01 pm
SQL!$@w$0ME - Wednesday, August 30, 2017 12:00 PMSQL!$@w$0ME - Thursday, January 26, 2017 11:20 AMHi Folks,
Any one has a script to script out user permissions from Azure SQL database? I was not able to find this in any articles/forums.
Many thanks!Any one has a script handy to script out permissions from Azure SQL database?
Thanks!
From what I understand, it's the same in Azure as it is in local metal systems. See the following. It has scripts in the examples but I do recommend that you read the entire article.
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-permissions-transact-sql
--Jeff Moden
Change is inevitable... Change for the better is not.
August 31, 2017 at 8:22 am
Jeff is right! I have ran Script B from the link above on Azure SQL Database and I get something like: (snippet)
name type_desc authentication_type_desc state_desc permission_name ObjectName
Jimmy SQL_USER DATABASE GRANT SELECT SalesLT.vGetAllCategories
August 31, 2017 at 8:47 am
BLOB EATER - Thursday, August 31, 2017 8:22 AMJeff is right! I have ran Script B from the link above on Azure SQL Database and I get something like: (snippet)name type_desc authentication_type_desc state_desc permission_name ObjectName
Jimmy SQL_USER DATABASE GRANT SELECT SalesLT.vGetAllCategories
I cannot fully agree with you that accounts/permissions are similar to earlier versions of native SQL Server. How do you script out azure AAD authentication credentials/permissions in sql azure db which does not exist in earlier versions of SQL.
user.name@domain.com(azure) vs DOMAIN\username(native sql)
Thanks!
August 31, 2017 at 3:24 pm
Dunno but I'm pretty sure that a decent look in Yabingooglehoo will help. It's what I'd have to do if I were looking for such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2018 at 11:19 am
Hi Folks,
Any one has a script to script out user permissions from Azure SQL database? I was not able to find this in any articles/forums.
Many thanks!
Hey SQL!@w$0M,
I use this script to generate a Matrix of the rights on a database:
DECLARE @permissionlevels TABLE
(
PermissionLevel NVARCHAR(256)
)
INSERT INTO @permissionlevels
SELECT DISTINCT pp.name AS PermissionLevel
FROM sys.database_role_members roles
LEFT JOIN sys.database_principals p
ON roles.member_principal_id = p.principal_id
LEFT JOIN sys.database_principals pp
ON roles.role_principal_id = pp.principal_id
DECLARE @permissionlevel_columns NVARCHAR(1000)
SET @permissionlevel_columns = ''
SELECT @permissionlevel_columns += '[' + PermissionLevel + '],' FROM @permissionlevels
SET @permissionlevel_columns = STUFF(@permissionlevel_columns, LEN(@permissionlevel_columns), 1, '')
DECLARE @sqlstatement NVARCHAR(MAX)
SET @sqlstatement = 'SELECT ServerName '
+ ', DBName '
+ ', UserName '
+ ', TypeOfLogin '
+ ',' + @permissionlevel_columns
+ 'FROM ( '
+ 'SELECT @@servername AS ServerName '
+ ', db_name(db_id()) AS DBName '
+ ', p.name AS UserName '
+ ', p.type_desc AS TypeOfLogin '
+ ', pp.name AS PermissionLevel '
+ ', pp.type_desc AS TypeOfRole '
+ ', ''x'' AS Autorized '
+ 'FROM sys.database_role_members roles '
+ 'LEFT JOIN sys.database_principals p '
+ 'ON roles.member_principal_id = p.principal_id '
+ 'LEFT JOIN sys.database_principals pp '
+ 'ON roles.role_principal_id = pp.principal_id '
+ ') a '
+ 'PIVOT '
+ '( '
+ 'MAX(Autorized) '
+ 'FOR PermissionLevel IN (' + @permissionlevel_columns + ') '
+ ') AS pv '
+ 'ORDER BY UserName '
EXEC (@sqlstatement)
May 8, 2019 at 10:15 am
my 5 cents :
select
isnull(object_schema_name(major_id,db_id()) +'.'+ object_name(major_id),
case when dp.class_desc='SCHEMA' then 'db schema '+quotename(schema_name(dp.major_id)) else dp.class_desc end ) [object]
,permission_name [permission]
, user_name(dp.grantee_principal_id) [GrantedTo]
, user_name(dp.grantor_principal_id) [GrantedBy]
, o.is_ms_shipped
, o.type_desc
,dp.state_desc
from sys.database_permissions dp
left join sys.objects o on dp.major_id=o.object_id
order by 1
option (recompile)
September 11, 2020 at 6:42 pm
select rp.name as database_role, mp.name as database_user
from sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
order by rp.name
-- object permissions by role
SELECT DISTINCT rp.name,
ObjectType = rp.type_desc,
PermissionType = pm.class_desc,
pm.permission_name,
pm.state_desc,
ObjectType = CASE
WHEN obj.type_desc IS NULL
OR obj.type_desc = 'SYSTEM_TABLE' THEN
pm.class_desc
ELSE obj.type_desc
END,
s.Name as SchemaName,
[ObjectName] = Isnull(ss.name, Object_name(pm.major_id))
FROM sys.database_principals rp
INNER JOIN sys.database_permissions pm
ON pm.grantee_principal_id = rp.principal_id
LEFT JOIN sys.schemas ss
ON pm.major_id = ss.schema_id
LEFT JOIN sys.objects obj
ON pm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas s
ON s.schema_id = obj.schema_id
--WHERE rp.type_desc = 'DATABASE_ROLE'
-- AND pm.class_desc <> 'DATABASE'
ORDER BY rp.name,
rp.type_desc,
pm.class_desc
select schema_name(schema_id) as schemanames,
user_name(s.principal_id) as usernames
from sys.schemas As s
Tung Dang
Azure and SQL Server DBA Contractor / Consultant
DataZip
September 18, 2020 at 6:52 pm
Enjoy, this query is made for Azure SQL. Please modify it accordingly
DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'thinkbrainbox@alphaonedata.com'
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
SELECT @DatabaseUserID = [sysusers].[uid]
FROM [dbo].[sysusers]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @DatabaseUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'CREATE USER [' + @DatabaseUserName + '] '+ ( select case when type_desc in ('EXTERNAL GROUP','EXTERNAL_USER') then 'FROM EXTERNAL PROVIDER'
when type_desc in ('SQL USER') then 'WITH PASSWORD = ' END
from sys.database_principals where name = @DatabaseUserName) + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT [name]
FROM [dbo].[sysusers]
WHERE [uid] IN (SELECT [groupuid] FROM [dbo].[sysmembers] WHERE [memberuid] = @DatabaseUserID )
OPEN _sysusers
FETCH NEXT FROM _sysusers INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH NEXT FROM _sysusers INTO @RoleName
END
SET @msgStatement = CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT([sysobjects].[id]), '[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects] ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH NEXT FROM _sysobjects INTO @ObjectID, @ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
END
*** Please network with me and feel free to IM if you have any more DBA questions and concerns
https://www.linkedin.com/in/james-rossi-9094651b7/
james rossi
Senior SQL Server DBA
Senior Software Developer
Alpha One Data
Brain Box SQL Mobile DBA
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply