Permissions required to alter a database file

Posted on

Question :

Totally at the beginning in administering a database.

Using EMS SQL Manager … latest version. How do I over come below

ALTER DATABASE joneslocker2
MODIFY FILE (
    NAME = joneslocker2, 
    MAXSIZE = UNLIMITED )
GO

which results in an error:

User does not have permission to alter database ‘joneslocker2’, the
database does not exist, or the database is not in a state that allows
access checks.

I don’t have a clue what to set next.

Answer :

You need to GRANT ALTER DATABASE permission to the user.

GRANT ALTER ON DATABASE:: joneslocker2 TO username

Below script will help you to find what permissions are assigned at the database level :

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission] 
FROM [sys].[database_permissions] sec 
  JOIN [sys].[database_principals] prin 
    ON sec.[grantee_principal_id] = prin.[principal_id] 
WHERE sec.class = 0 
ORDER BY [User], [Permission];

Check my answer here for an example on why you are getting the error.

Refer to : Database Engine Permission Basics

Granting just alter to the specific database does not allow for the rename.

You must also grant permissions at the server level and in master.

USE master;  
ALTER SERVER ROLE [dbcreator] ADD MEMBER [DOMAINUser-Name]
GO

USE master;    
ALTER ROLE [db_datareader] ADD MEMBER [DOMAINUser-Name];
ALTER ROLE [db_datawriter] ADD MEMBER [DOMAINUser-Name];

USE [AdventureWorks]
ALTER ROLE [db_owner] ADD MEMBER [DOMAINUser-Name];  

Leave a Reply

Your email address will not be published. Required fields are marked *