3

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.

  • What are the permissions given to user , trying to alter database as mentioned above? – KASQLDBA May 27 '15 at 16:14
  • Is the user ( or login ) DBO? – Racer SQL May 27 '15 at 17:13
4

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 alter permission does not allow a user to rename the database. Even if the user has db_owner permission on the database and dbcreator at the server level. – jclabonde Feb 7 '18 at 14:40
  • @jclabonde read OPs question carefully. OP is asking for modifying file size to unlimited and NOT asking to rename the database. What I posted works (i have tested it and then posted as answer). The point you are making is kind of valid when you have to rename the db (but that is not what OP is asking). Also to rename the db, you dont need to be in reader and writer role in master. – Kin Shah Feb 8 '18 at 18:13
0

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 [DOMAIN\User-Name]
GO

USE master;    
ALTER ROLE [db_datareader] ADD MEMBER [DOMAIN\User-Name];
ALTER ROLE [db_datawriter] ADD MEMBER [DOMAIN\User-Name];

USE [AdventureWorks]
ALTER ROLE [db_owner] ADD MEMBER [DOMAIN\User-Name];  
  • I'm downvoting for recommending using a deprecated solution – Mark Sinkinson Feb 7 '18 at 15:22
  • Why you need reader and writer in master ? – Kin Shah Feb 8 '18 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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