SQL Server Cannot alter the role and grant permission to yourself in MS SQL

wsxa1bj1 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(92)

I have a user assigned with the Server Role which is not sysadmin. Although the user has the ability to create a database, the user cannot create tables within it. In my attempt to modify the user's Role and grant additional permissions, such as the ability to create or control tables, I encountered an error stating, "Cannot alter the role 'db_ddladmin', because it does not exist or you do not have permission." and "Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself." respectively. The application dynamically generates the database and tables, making it impractical to manually grant permissions in SSMS. I utilized the following scripts

ALTER ROLE db_ddladmin ADD MEMBER newuser

GRANT CREATE TABLE TO newuser

When I try to run the following it does not work either.

CREATE DATABASE TEST2
GO
USE TEST2
GO
EXEC sp_addrolemember 'db_ddladmin', 'newuser';
CREATE TABLE DBO.SOMETABLE (ID INT)
GO

sql-server

来源：https://stackoverflow.com/questions/76612678/cannot-alter-the-role-and-grant-permission-to-yourself-in-ms-sql

1条答案

按热度按时间

mzillmmw1#

The 'db_ddladmin' is not a default Server Role that SQL Server Provides (unless somebody created a new custom role).

In Order to modify / create / assign roles to a Login in SQL Server, you yourself need to login first with SysAdmin role. This role has all the rights in SQL Server and ability to Grant and Revoke Permissions.

Also in SQL Server, there are two types of users. Based on these concepts, you can create permissions depending on your need.