We can see that our login has view any database server scoped permission and create database permission in the master database. SELECT * FROM fn_my_permissions(null,'server') SELECT * FROM fn_my_permissions(null,'database') Have a look at the server scoped and database (in the master database) scoped permissions EXECUTE AS LOGIN = 'OwnerRoleMember' Recreate the database if you dropped it during the last tests, map the login into the master database and grant the permission. REVERT -exit the security context from earlierĮXEC sp_droprolemember 'db_owner','OwnerRoleMember'Ĭreate database permission in the master database: If you didn’t delete the database and try the restores then you need to clean up and remove the db_owner membership. If we drop the existing database and rerun the restores they will not surprisingly error again. Impersonate the login/user we created: EXECUTE AS LOGIN = 'OwnerRoleMember'Īll four restores error. USE TestRestoreĬREATE USER FROM LOGIN ĮXEC sp_addrolemember 'db_owner','OwnerRoleMember' Map the user and add to the db_owner role. Now I’ll gradually elevate permissions and see what happens with the restores, starting with.
Apex sql no database permissions password#
WITH PASSWORD = '123', CHECK_POLICY = OFF USE ĪLTER AUTHORIZATION ON DATABASE::TestRestore TO sa To test I’ll create a database and take a backup, and then create a SQL Server login. So I’m interested in how dbcreator server role membership, db_owner database role membership and create database permission in the master database relate to the following three restore scenarios: restore into an existing database, restore into an existing database with replace, and restore to a new database.
Apex sql no database permissions windows#
SQL Server will not allow a database to be owned by a login created from a Windows group, leaving individual windows accounts or SQL Server accounts.) (Interestingly, if a DBA chooses not to have databases owned by the sa account, they face further restrictions. Now the principal of least privilege means I’m not really interested in members of the sysadmin server role.Īlso, according to generally accepted practice our database should be owned by the sa account.
Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.” “RESTORE permissions are given to roles in which membership information is always readily available to the server. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner ( dbo) of the database.” “If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. I’m interested in situations where a DBA needs to allow certain users the ability to restore a given database, for example refreshing UAT, while maintaining minimum permission levels and following accepted best practice. I came across a couple of subtle gotchas around the permissions required to restore a database in SQL Server 2008+ recently.