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

This article will explain in detail how to create ID and permission control in the database. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

USE [master]

GO

CREATE LOGIN WITH PASSWORD=,

DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

USE [master]

GO

--Query View

grant view server state to

GO

USE [datayesdb]

GO

CREATE USER FOR LOGIN WITH DEFAULT_SCHEMA=[dbo]

GO

ALTER ROLE [db_ddladmin] ADD MEMBER

GO

ALTER ROLE [db_datareader] ADD MEMBER

GO

--View execution plan

grant showplan to

GO

USE [datayesdb]

GO

--Prevent dba_zhangning users from deleting tables

create trigger tr_denytable_datayesdb

on database

for DROP_TABLE, ALTER_TABLE

as

if (SUSER_SNAME()='dba_zhangning')

begin

rollback

end

GO

USE [spiderdb]

GO

CREATE USER FOR LOGIN WITH DEFAULT_SCHEMA=[dbo]

GO

ALTER ROLE [db_ddladmin] ADD MEMBER

GO

ALTER ROLE [db_datareader] ADD MEMBER

GO

grant showplan to

GO

USE [spiderdb]

GO

--Prevent dba_zhangning users from deleting tables

create trigger tr_denytable_spiderdb

on database

for DROP_TABLE, ALTER_TABLE

as

if (SUSER_SNAME()='dba_zhangning')

begin

rollback

end

GO

About "how to create ID and permission control in the database" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

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.