SQL script to check existing accounts permission and modify them with proper permisison

michalek19
michalek19 used Ask the Experts™
on
Hi

I need SQL script to check existing accounts permission with option to

Modify them if necessary to existing database
Add account if missing to existing database

Thx, M
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please see if this helps -

select sys.schemas.name 'Schema'
   , sys.objects.name Object
   , sys.database_principals.name username
   , sys.database_permissions.type permissions_type
   ,     sys.database_permissions.permission_name
   ,      sys.database_permissions.state permission_state
   ,     sys.database_permissions.state_desc
   ,     state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS 
from sys.database_permissions join sys.objects on sys.database_permissions.major_id =      sys.objects.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id join sys.database_principals on sys.database_permissions.grantee_principal_id =      sys.database_principals.principal_id order by 1, 2, 3, 5

Open in new window


from - https://www.sqlservercentral.com/Forums/Topic1235858-391-1.aspx

Author

Commented:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '2'.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please use this -

select sys.schemas.name 'Schema'
   , sys.objects.name Object
   , sys.database_principals.name username
   , sys.database_permissions.type permissions_type
   ,     sys.database_permissions.permission_name
   ,      sys.database_permissions.state permission_state
   ,     sys.database_permissions.state_desc
   ,     state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS 
from sys.database_permissions join sys.objects on sys.database_permissions.major_id =      sys.objects.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id join sys.database_principals on sys.database_permissions.grantee_principal_id =      sys.database_principals.principal_id 

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Do you have a list of users that you want to check/add permissions for?

The general structure of such code would be as below.  You could create a proc in the master db to allow you to easily run the code in the context of any user db.

USE [db_name_to_check_permissions_for];
GO
IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = 'user_to_check')
BEGIN
    CREATE USER [user_to_check] FROM LOGIN [user_to_check];
END /*IF*/
GO
IF NOT EXISTS(SELECT 1 FROM sys.database_role_members
WHERE role_principal_id = DATABASE_PRINCIPAL_ID('user_to_check')
AND ember_principal_id = DATABASE_PRINCIPAL_ID('db_datareader')
BEGIN
    --if on SQL 2012 or later, you should use ALTER ROLE instead
    EXEC sp_addrolemember 'db_datareader', 'user_to_check'
END /*IF*/
GO
IF NOT EXISTS(SELECT 1 FROM sys.database_role_members
WHERE role_principal_id = DATABASE_PRINCIPAL_ID('user_to_check')
AND ember_principal_id = DATABASE_PRINCIPAL_ID('db_datawriter')
BEGIN
    --if on SQL 2012 or later, you should use ALTER ROLE instead
    EXEC sp_addrolemember 'db_datawriter', 'user_to_check'
END /*IF*/

etc.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '2'.
When you copy the code and when you paste it, if it's comes with leading line numbers, delete those numbers before running the query.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
What does a very-generic permissions SELECT have to do anyway with this specific q: creating a db and then adding permissions as needed to that?

The results from the query above against a newly-created db will list the default permissions, as there's nothing else there to list.

Author

Commented:
Scott Pletcher, I have a lot of user to check against database

db_name_to_check_permissions_for  --> here I have to type database name

IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = 'user_to_check')  --> in user to check should I write here domain\account that I search for

CREATE USER [user_to_check] FROM LOGIN [user_to_check];  'user_to_check')  --> in user to check should I write here domain\account

can you please explain?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
If it's a lot of users, ultimately we'll create a table of user names to be processed.

If it's a lot of permissions, we'll create a table of permissions to grant / verify are present.

The code just checks to see if:
1) the desired user exists in the db -- if not, it creates it
2) the desired permission for the desired user exists in the db -- if not, it adds it

While testing the code, we run it hard-coded for a single user and the desired permissions.  Once the code is working, we modify the code to be invoked for multiple users and multiple permissions.

Author

Commented:
The code which you provided is complete?  When I ran it got errors,
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
No, as I stated, that's the "general structure" of the code. I couldn't provide complete code because you didn't provide specific user names nor specific permission s.

Author

Commented:
A list of users

ModD\Miatest
ModD\Robtest
ModD\Jobtest
ModD\Christest
ModD\Bentest
ModD\Anatest
ModD\Jentest
ModD\Pattest


Permission for the desired user exists in the db

DBO with

•      bulk updates
•      stats monitoring
•      VIEW SERVER STATE
•      Inserts
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Please use like below -

You need provide YourDatabaseName and LOGIN Name in the below script to work.

IF OBJECT_ID('tempdb..#Users') IS NOT NULL
                DROP TABLE #Users

SELECT UserName INTO #Users FROM 
(
	SELECT '[ModD\Miatest]' UserName UNION ALL
	SELECT '[ModD\Robtest]'  UNION ALL
	SELECT '[ModD\Jobtest]'  UNION ALL
	SELECT '[ModD\Christest]'  UNION ALL
	SELECT '[ModD\Bentest]' UNION ALL
	SELECT '[ModD\Anatest]' UNION ALL
	SELECT '[ModD\Jentest]' UNION ALL
	SELECT '[ModD\Pattest]'
)r

IF OBJECT_ID('tempdb..#TempUsers') IS NOT NULL
                DROP TABLE #TempUsers

SELECT * INTO #TempUsers FROM #Users
DECLARE @UserName AS VARCHAR(100)

WHILE EXISTS ( SELECT TOP 1 1 FROM #TempUsers )
BEGIN

	SELECT TOP 1 @UserName =  UserName FROM #TempUsers

	/* Create User if not exists */
	EXEC
	( 
	'
		USE [YourDatabaseName];
		GO
		
		IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = ' + @UserName + ')
		BEGIN
			CREATE USER ' + @UserName + ' FROM LOGIN yourLoginName ;
		END 
	' 
	)

	/* Provide reader access */
	EXEC
	( 
	'
		USE [YourDatabaseName];
		GO

		IF NOT EXISTS(
		SELECT 1 FROM sys.database_role_members
		WHERE role_principal_id = DATABASE_PRINCIPAL_ID(' +  @UserName + ')
		AND ember_principal_id = DATABASE_PRINCIPAL_ID(''db_datareader'')
		BEGIN
			EXEC sp_addrolemember ''db_datareader'', ' + @UserName + '
		END
	'
	)
	
	/* Provide writer access */
	EXEC
	( 
	'
		USE [YourDatabaseName];
		GO

		IF NOT EXISTS(
		SELECT 1 FROM sys.database_role_members
		WHERE role_principal_id = DATABASE_PRINCIPAL_ID(' +  @UserName + ')
		AND ember_principal_id = DATABASE_PRINCIPAL_ID(''db_datareader'')
		BEGIN
			EXEC sp_addrolemember ''db_datawriter'', ' + @UserName + '
		END
	'
	)

	DELETE FROM #TempUsers WHERE UserName = @UserName
END

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial