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

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
michalek19Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
michalek19Author 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'.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Pawan KumarDatabase ExpertCommented:
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

0
 
Scott PletcherSenior DBACommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Scott PletcherSenior DBACommented:
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.
0
 
michalek19Author 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?
0
 
Scott PletcherSenior DBACommented:
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.
0
 
michalek19Author Commented:
The code which you provided is complete?  When I ran it got errors,
0
 
Scott PletcherSenior DBACommented:
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.
0
 
michalek19Author 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.