Cannot alter the role 'db_owner', because it does not exist or you do not have permission.
Question
Tuesday, November 10, 2015 11:42 AM
I'm trying to avoid having logins with sysadmin. So instead I tried the CONTROL SERVER permission. The problem is that the login with the CONTROL SERVER permission can't add members to the role db_owner.
I tried both
EXEC sp_addrolemember N'db_owner', N'MyDomain\MyUser'
ALTER ROLE db_owner ADD MEMBER [MyDomain\MyUser]
All I get is:
Msg 15151, Level 16, State 1, Line 1
Cannot alter the role 'db_owner', because it does not exist or you do not have permission.
All replies (6)
Thursday, November 12, 2015 12:54 PM âś…Answered
DBCC CHECKTABLE requires you to be a member of db_owner role.
Consider creating a stored procedure signed by a certificate with the needed permissions. Erland's article (http://www.sommarskog.se/grantperm.html) has examples for TRUNCATE and BULK INSERT that you can adapt for your needs.
Dan Guzman, SQL Server MVP, http://www.dbdelta.com
Tuesday, November 10, 2015 12:14 PM
Well , Is it possible first of all to allow a login to add members to the db_owner role and then grant them CONTROL SERVER permissions?
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Tuesday, November 10, 2015 3:01 PM
And how do I allow a login to add members to the db_owner (besides being sysadmin)?
Tuesday, November 10, 2015 5:03 PM
As the documentation states:
Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role membership. However, only members of the db_owner database role can add members to the db_owner fixed database role.
So what you seek seems impossible. If you describe what you are trying to accomplish, someone might respond with an alternative approach. Why would someone who is not a member of this role be adding others to the role?
Tuesday, November 10, 2015 5:08 PM
Why do your users need membership in the db_owner role? Perhaps they can get by with CONTROL DATABASE permission. Of course CONTROL DATABASE is covered by the CONTROL SERVER permission so they already have that.
The basic problem is that you are mixing the pre-2005 permission system (fixed roles) with the 2005 and later permission system (CONTROL ...) and the two systems sometimes don't play well together. For what it's worth, we encourage customer to migrate to the new, more granular permission system whenever possible. Permissions poster http://go.microsoft.com/fwlink/?LinkId=229142.
Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
Thursday, November 12, 2015 12:22 PM
DBCC CHECKTABLE requires you to be a member of db_owner role.