0

I'm trying to run the following command with a user account(XXX_Admin):

ALTER DATABASE [XXX] SET TRUSTWORTHY ON;

This works with the "SA" account, but with my user account(XXX_Admin) I get:

Msg 5011, Level 14, State 5, Line 1 User does not have permission to alter database 'XXX', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

I've checked, and this user account should have the correct permissions:

(XXX Database)

enter image description here

(Login - Server Rules)

enter image description here

(Login - User Mapping)

enter image description here

I've also verified that the login and user are synced with the following commands:

EXEC sp_change_users_login 'update_one', 'XXX_Admin', 'XXX_Admin'

EXEC sp_change_users_login 'AUTO_FIX', 'XXX_Admin'

GRANT ALTER ON DATABASE:: XXX TO XXX_Admin

Now I'm really at a loss as to what to check next... As so often with SQL Permissions issues, I grant all access to my account that I can think of but am still unable to run commands. Am I missing something?

closed as off-topic by David Browne - Microsoft, Paul White Jun 11 '18 at 1:02

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – David Browne - Microsoft, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Just a guess, but permissions are based on the most restrictive set, so if you have any roles or explicit DENYs, they would counter-act the permissions you are expecting. I would remove the non-relevant roles from both the Login and the User levels: you only need sysadmin for the Login / Server-level, and db_owner for the User / Database-level (and you cannot get rid of Public). Try that first. – Solomon Rutzky Jun 8 '18 at 14:37
  • Also, why are you enabling TRUSTWORTHY in the first place? I have found at most 1 case for enabling it, and that was definitely an edge case: PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining. – Solomon Rutzky Jun 8 '18 at 14:37
  • To answer you second question first :), the reason I'm using it is because the amount of time(in my experience) required to refactor my databases to use the proper SQL permission schema far outweighs the amount of value that the new feature would provide, struggles around SQL permission, and the associated problems that they cause(in production) are one of my chief headaches, and my application doesn't really use SQL permissions... Actually if I could turn them off I probably would. – David Rogers Jun 8 '18 at 15:04
  • Tried your second approach, no luck, but I updated my question with more the new permissions. – David Rogers Jun 8 '18 at 15:20
  • Is your XXX_Admin login a SQL Server Login or a Windows Login? If it's a Windows Login, do you also have logins for Windows Groups, and if so, is your Windows Login in one or more of those groups? While logged in as XXX_Admin, execute the following: USE [XXX]; SELECT * FROM fn_my_permissions (NULL, 'DATABASE');. Does the ALTER show up? – Solomon Rutzky Jun 8 '18 at 16:25
0

Oops, Look like I got one of my X's mixed up in the database name, apparently if you run:

ALTER DATABASE [XXX_DATABASEDOESNOTEXIST] SET TRUSTWORTHY ON;

You'll get this error... :(

  • D'oh! I do suppose that one of the 3 options in the error message is that the DB does not exist. – Solomon Rutzky Jun 8 '18 at 19:31

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