March 23, 2017 at 2:25 am
Hi everyone,
We have a newly installed SQL 2016 instance (SP1).
We've created a new user - windows authentication on the server. The user can login to the server, and database.
Because it's a DEV server we've given the user db_owner access to the selected user database.
The user wants to rename some tables on this database. Every time that he tries to rename the table (using T-SQL), he get this err:
He is connected to the User DB - so it's not that he is in a different DB.
Any ideas?
March 23, 2017 at 3:04 am
Wandrag - Thursday, March 23, 2017 2:25 AMHi everyone,
We have a newly installed SQL 2016 instance (SP1).
We've created a new user - windows authentication on the server. The user can login to the server, and database.
Because it's a DEV server we've given the user db_owner access to the selected user database.
The user wants to rename some tables on this database. Every time that he tries to rename the table (using T-SQL), he get this err:
Msg 229, Level 14, State 5, Procedure sp_rename, Line 10
The EXECUTE permission was denied on the object 'sp_rename', database 'mssqlsystemresource', schema 'sys'.He is connected to the User DB - so it's not that he is in a different DB.
Any ideas?
Check if the user has an ALTER permission on the tables.
😎
Can you post the actual syntax the developer is using?
March 23, 2017 at 6:42 am
Eirikur Eiriksson - Thursday, March 23, 2017 3:04 AMWandrag - Thursday, March 23, 2017 2:25 AMHi everyone,
We have a newly installed SQL 2016 instance (SP1).
We've created a new user - windows authentication on the server. The user can login to the server, and database.
Because it's a DEV server we've given the user db_owner access to the selected user database.
The user wants to rename some tables on this database. Every time that he tries to rename the table (using T-SQL), he get this err:
Msg 229, Level 14, State 5, Procedure sp_rename, Line 10
The EXECUTE permission was denied on the object 'sp_rename', database 'mssqlsystemresource', schema 'sys'.He is connected to the User DB - so it's not that he is in a different DB.
Any ideas?
Check if the user has an ALTER permission on the tables.
😎Can you post the actual syntax the developer is using?
If the user has membership of the db_owner fixed role then this grants CONTROL of the database, ALTER is a permission inherited and implied when CONTROL is held, so no issue there.
Does the user actually have db_owner membership?
Please also post the query the user is running
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 23, 2017 at 10:40 pm
Hi,
Yes - double checked - do have dw_owner access.
The query that we ran:
When I run it, it work just fine (I'm SA on the server).
Public access was revoked to some system stored procedures (part of the group security rules), and we had to give specific access again - for example:
grant Execute ON [sys].fn_syspolicy_is_automation_enabled TO ;
grant Execute ON [sys].[xp_instance_regread] TO ;
And for some reason had to give execute permissions for the user within MSDB.
not sure if this have anything to do with it?
March 28, 2017 at 3:46 am
Anything else that I can check?
March 28, 2017 at 4:11 am
Wandrag - Tuesday, March 28, 2017 3:46 AMAnything else that I can check?
unable to reproduce this, a careful review of permissions would be the first step
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 28, 2017 at 8:16 am
Wandrag - Thursday, March 23, 2017 10:40 PMWhen I run it, it work just fine (I'm SA on the server).Public access was revoked to some system stored procedures (part of the group security rules), and we had to give specific access again - for example:
grant Execute ON [sys].fn_syspolicy_is_automation_enabled TO ;
grant Execute ON [sys].[xp_instance_regread] TO ;
And for some reason had to give execute permissions for the user within MSDB.not sure if this have anything to do with it?
Membership in sysadmins means it bypasses security checks so that will always work.
Normally the permissions on sp_rename aren't specifically granted to any role, users, etc as the permissions to execute are by default granted to public. So if you changed the permissions on the public role, that could be what caused the issue. As Perry said, you need to check the permissions. Look at what the permissions are for the public role in master. By default it would have execute on sp_rename.
Sue
March 28, 2017 at 9:11 am
Wandrag - Thursday, March 23, 2017 10:40 PMHi,
Yes - double checked - do have dw_owner access.The query that we ran:
Exec sp_rename 'Product.AgeCalculationBasis', 'Product.AgeCalculationBasiss'When I run it, it work just fine (I'm SA on the server).
Public access was revoked to some system stored procedures (part of the group security rules), and we had to give specific access again - for example:
grant Execute ON [sys].fn_syspolicy_is_automation_enabled TO ;
grant Execute ON [sys].[xp_instance_regread] TO ;
And for some reason had to give execute permissions for the user within MSDB.not sure if this have anything to do with it?
Something is missing in this picture, what are the permissions revoked on the db_owner?
😎
March 29, 2017 at 4:06 am
Eirikur Eiriksson - Tuesday, March 28, 2017 9:11 AMWandrag - Thursday, March 23, 2017 10:40 PMHi,
Yes - double checked - do have dw_owner access.The query that we ran:
Exec sp_rename 'Product.AgeCalculationBasis', 'Product.AgeCalculationBasiss'When I run it, it work just fine (I'm SA on the server).
Public access was revoked to some system stored procedures (part of the group security rules), and we had to give specific access again - for example:
grant Execute ON [sys].fn_syspolicy_is_automation_enabled TO ;
grant Execute ON [sys].[xp_instance_regread] TO ;
And for some reason had to give execute permissions for the user within MSDB.not sure if this have anything to do with it?
Something is missing in this picture, what are the permissions revoked on the db_owner?
😎
agreed, hence my suggestion to re visit, something is revoked or denied i would say
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply