Shulou(Shulou.com)05/31 Report--

This article editor for you detailed introduction "SQLServer how to achieve only to create table permissions", detailed content, clear steps, details handled properly, I hope that this "how to achieve SQLServer only to create table permissions" article can help you solve doubts, the following slowly deepen with the editor's ideas, together to learn new knowledge.

Background

The client asked a question today. I want to create a new account for outsiders to use, but I just want to give him permission to create tables, how to do this. You may think the problem is simple at first.

I create a new login account A

USE [master] GOCREATE LOGIN [A] WITH PASSWORD=N'123456', DEFAULT_DATABASE= [master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGO

Create user An at the database level

USE [security_test] GOCREATE USER [a] FOR LOGIN [a] WITH DEFAULT_SCHEMA= [dbo] GO

Give permission to create a table

GRANT CREATE TABLE TO A

Then give him the authority to create the table.

Now try to build a table

CREATE TABLE test (id int)

Prompt for the following information:

What's going on?

Solution 1

To create a table, each table requires an owner, which is the schema name. For our create table statement CREATE TABLE test (id int), it actually defaults to the dbo schema.

A user has the right to create the table, and also needs to have the permission to modify the dbo schema. So we need to:

GRANT ALTER ON SCHEMA::dbo TO A Teng Go

But at this time, we will encounter another problem, that is, after adding this permission, user A can also do drop table, alter table and other operations in addition to create table.

So we need to create a DDL trigger to prevent other operations.

CREATE TRIGGER db_trigger_BlockNonTableDDLON DATABASE FOR DDL_DATABASE_LEVEL_EVENTSAS BEGIN IF IS_MEMBER ('A') = 1 BEGIN DECLARE @ TriggerEventText nvarchar (max); SET @ TriggerEventText = EVENTDATA () .value ('(/ EVENT_INSTANCE/TSQLCommand/CommandText) [1]', 'nvarchar (max)') IF NOT ((@ TriggerEventText LIKE 'CREATE TABLE%')) BEGIN RAISERROR (@ TriggerEventText, 16,1) ROLLBACK TRANSACTION; END END; END GO solution 2

If the table you create does not need to use the default dbo schema

Then you can create a separate shchema for outsiders. Create a new schema schema

Create schema schema1 authorization dbogo

And then

Grant create table to Agrant alter, insert on schema::schema1 to A

At this point, A can still create and delete tables, but he directly creates and deletes tables that belong to its schema. This also raises the question of limiting permissions.

After reading this, the article "how SQLServer can only grant permission to create tables" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself. If you want to know more about related articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.