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];