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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.