Sql-server – Permissions on SQL Server Sequences

permissionssequencesql-server-2016

The AD Group to which I belong has db_owner permissions on database LandingZone. The application I am debugging, that is running under my ID, is doing a SELECT NEXT VALUE FOR ThisSchema.ThatSequence. I am getting the following error:

Msg 229, Level 14, State 5, Line 6
The UPDATE permission was denied on the object 'ThatSequence', database 'LandingZone', schema 'ThisSchema'.

I think I'm running into a Least Privilege issue but the production DBAs disagree.

Any suggestions?

Best Answer

db_owner is supposed to have all permissions within given db, so you should be able to update that sequence without any problem - if your app really runs under your UserID

1) Check the connection string that app is using

If connection string does not contain Integrated Security = True or Trusted Connection = True, then you have to add those to connection string

2) if app is running from within some web server (IIS Web server etc.), and its included into some Application Pool, you need to make sure this Application Pool runs under security context of your Windows User...

3) to verify that your application is really connecting under your Windows credentials to SQL Server, while the app is connected to DB, check out following DMVs at SQL Server:

select * from sys.dm_exec_sessions
select * from sys.dm_exec_connections

(those can be linked by session_id, find session/connection of your application and make sure it runs under your Windows credentials (login_name "YourDomain\YourUserName")

4) if App is running and connecting to DB using your Windows creds, and you still have this permission issue, then you have to make sure your Windows user if really is in AD Group, and AD Group is really added to db_owner role at given database...

Related Question