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
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.
Do refer below two screenshots shared at last.
Please refer below article from Microsoft to understand roles.
https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver16
Please refer below article to Understand Users.
https://www.educba.com/sql-users/