SQL server database tob under 50 users, in addition to Xx the rest all users can query tob database under the table. A, table b how to set up?

CodePudding user response:

1, create a login
2, create user
3, empowerment

CodePudding user response:

According to operation again almost, pay attention to the dividing line is different steps,

 USE test 
GO 
The CREATE TABLE a (id INT) 
The CREATE TABLE b (id INT) 
The CREATE TABLE c (id INT) 
The CREATE TABLE d (id INT) 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- more than for test library, the test table -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
GO 

Xx - create a login account, password is xx 
USE [master] 
GO 
The CREATE LOGIN [xx] WITH PASSWORD=N 'xx' DEFAULT_DATABASE=[test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO 
- the test database is created on the user xx 
USE [test] 
GO 
The CREATE USER [xx] FOR the LOGIN [xx] 
GO 
- the user on the role of xx in the test library for db_owner 
USE [test] 
GO 
The ALTER roles [db_owner] ADD MEMBER [xx] 
GO 
- to cancel xx on the table a and table b, add and delete permissions 
DENY the SELECT, INSERT, UPDATE, DELETE, ALTER ON dbo. A. TO xx 
DENY the SELECT, INSERT, UPDATE, DELETE, ALTER ON dbo. B TO xx 


-- -- -- -- -- -- -- -- -- to xx to login after test to check a, b table -- -- -- -- -- -- -- -- 
USE [test] 
GO 
SELECT * FROM [dbo]. [a] 
GO 
SELECT * FROM [dbo] [b] 
GO 
/* 
Message 229, level 14, state 5, line 4 
Refused to objects' a '(' dbo' database 'test' architecture) SELECT privilege, 
Message 229, level 14, state 5, line 6 
Refused to objects' b '(' dbo' database 'test' architecture) SELECT privilege, 
*/