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

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

Select allOpen in new window

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

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '2'.

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 

Select allOpen in new window

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.

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.

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.

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?

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.

The code which you provided is complete?  When I ran it got errors,

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.

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