ALTER DATABASE permission error

  • ALTER DATABASE DB1

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    This is giving me the following error and I am in the sysadmin server role in the server.

    Msg 5011, Level 14, State 5, Line 1

    User does not have permission to alter database DB1', 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.

    The database DOES exist. I have queried the sys.databases as well to verify that it exists.

  • Just to make sure about your settings; can you post the output of the following query?

    SELECT IS_SRVROLEMEMBER('sysadmin') as I_am_SA, * FROM sys.databases WHERE name = 'DB1'

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Guras (10/18/2016)


    The database DOES exist. I have queried the sys.databases as well to verify that it exists.

    That's one thing eliminated, then. All that's left is either you don't have the necessary access, or the database is in a state where it can't be altered (RESTORING, maybe).

    John

  • Guras (10/18/2016)


    ALTER DATABASE DB1

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    This is giving me the following error and I am in the sysadmin server role in the server.

    Msg 5011, Level 14, State 5, Line 1

    User does not have permission to alter database DB1', 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.

    The database DOES exist. I have queried the sys.databases as well to verify that it exists.

    Please post the results of the following queries

    select user_access_desc, is_read_only, state_desc, is_in_standby

    from sys.databases where database_id = DB_ID('yourdb')

    select IS_SRVROLEMEMBER('sysadmin', name)

    from sys.server_principals

    where name = 'yourloginname'

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (10/19/2016)


    Guras (10/18/2016)


    ALTER DATABASE DB1

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    This is giving me the following error and I am in the sysadmin server role in the server.

    Msg 5011, Level 14, State 5, Line 1

    User does not have permission to alter database DB1', 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.

    The database DOES exist. I have queried the sys.databases as well to verify that it exists.

    Please post the results of the following queries

    select user_access_desc, is_read_only, state_desc, is_in_standby

    from sys.databases where database_id = DB_ID('yourdb')

    select IS_SRVROLEMEMBER('sysadmin', name)

    from sys.server_principals

    where name = 'yourloginname'

    This returns 0 but it's strange that my username is the member of the server role sysadmin if I check it in the Security section through SSMS.

  • This returns 0 but it's strange that my username is the member of the server role sysadmin if I check it in the Security section through SSMS.

  • Did you check any explicit permission was deny on alter for the DB1?

  • Guras (10/19/2016)


    it's strange that my username is the member of the server role sysadmin if I check it in the Security section through SSMS.

    are you connected to the correct instance 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply