0

We have a SQL Server 2008 Database.

I created a few Windows users, added them to a specific database, and granted them read write access with the scripts below:

CREATE LOGIN [PRUDENTIAL\X208476] FROM WINDOWS 
    WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

Use client
GO
CREATE USER [PRUDENTIAL\X208476] WITH DEFAULT_SCHEMA=dbo
GO
exec sp_addrolemember db_datareader, [PRUDENTIAL\X208476]
GO
exec sp_addrolemember db_datawriter, [PRUDENTIAL\X208476]
GO

They run a basic insert command as follows and receive the error shown below:

INSERT INTO [client].[dbo].[admin_user_role]
           ([user_id]
           ,[role_id]
           ,[last_acty_oper_id]
           ,[last_acty_dtm])
     VALUES
           ('X208480'
           ,'ADMIN'
           ,'x163798'        
           ,'2017-04-06 11:12:19.597')

Msg 229, Level 14, State 5, Line 1
The insert permission was denied on object ‘admin_user_role’, database ‘client’, schema ‘dbo’.

I tested this with another colleague of mine and he has no issue inserting into the table.

What other check/debugging can I do to resolve this issue?

2
  • Just throwing it out there -- you may not want to paste your company name and logins on the internet. Some may consider that a 'security risk'. Commented Apr 19, 2017 at 12:07
  • What do you get when you this? EXECUTE AS USER = 'PRUDENTIAL\X208476'; SELECT * FROM fn_my_permissions('dbo.admin_user_role', 'OBJECT') ORDER BY subentity_name, permission_name ; REVERT; GO Commented Apr 19, 2017 at 12:09

2 Answers 2

0

Please run this code to find out if you have a deny on that user under the column state_desc.

SELECT permission_name, 
   state_desc, 
   type_desc, 
   U.NAME, 
   Object_name(major_id) 
FROM   sys.database_permissions P 
   JOIN sys.tables T 
     ON P.major_id = T.object_id 
   JOIN sysusers U 
     ON U.uid = P.grantee_principal_id 
0

When you create the database user you have to ensure it is linked to the SQL Login you created for the windows account.

Instead of your code:

...
GO
CREATE USER [PRUDENTIAL\X208476] WITH DEFAULT_SCHEMA=dbo
GO
...

Try this code here:

...
GO 
CREATE USER [PRUDENTIAL\X208476] FOR LOGIN [PRUDENTIAL\X208476] WITH DEFAULT_SCHEMA=dbo
GO
...

In this way the SQL Login (which is based on a Windows Account) is linked to the database user on creation.

  • SQL Logins are stored in the master database.
  • Database Users are stored in the client (user's) database.

Finding all the permissions of a user

And if you would like to retrieve all the permissions of all your users in the client database, you could use the script provided in the stackoverflow article:

SQL Server query to find all permissions/access for all users in a database

The answer from Sean Rose seems to incorporate the most features

1
  • For windows login that is not mandatory but I would do that as a best practice. Commented Apr 19, 2017 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.