I have two users registered on the instance of SQL Server 2012: ape and ais2015 . Both server level users. I have ape as an administrator account, and ais2015 for a user (works only in read mode).

I need to restrict access to coko.or_user table for coko.or_user .

  1. What is the difference between a user type Пользователь SQL без имени входа and a Пользователь SQL c именем пользователя ? As in general, the first option is used, because with the help of it you cannot even authenticate using the SQL Server Management Studio.
  2. I tried to restrict access to the or_user table for ais2015 commands
 REVOKE SELECT ON coko.or_user TO ais2015 DENY DELETE ON coko.or_user TO ais2015

but still, ais2015 has access to or_user . Apparently because this server level account. It doesn't matter what happens with ais2015 , but how can I create a user who would have access to all the coko database tables except the or_user table?

  • 3
    you are confusing something here, there are logins and there are users of the database and these are different entities. - Konst
  • @Konst if I understand correctly, then I have 2 logins (ape and ais2015) and 2 пользователя , whose names coincide with the names of logins (ape and ais2015). So? - Adam

1 answer 1

As for the second point, then it is possible in REVOKE SELECT . REVOKE removes the permission previously issued with GRANT (or the ban set with DENY ). If the permission / prohibition was not made, then the command is executed, but it has no effect. If the permission to access an object was not previously granted using GRANT , then DENY should be used instead of REVOKE to REVOKE it. Also with your REVOKE SELECT you could lift the ban if it was set by making a selection from the table.

If you do it from scratch, then to achieve what you want, you should have something like this.

Create a login to access the server and the database user associated with this login:

 USE [master] GO CREATE LOGIN [TestLogin] WITH PASSWORD=N'123' GO USE [DbName] GO CREATE USER [DbUser] FOR LOGIN [TestLogin] GO

I note that in this case the username of the database does not match the login name to the server, but they can also be the same (by the way, if you create a login through a dialog in SSMS and do it mapping in the database user, then by default it happens, due to what confusion may arise).

Now let the user read the data, but disable it for the selected object:

 USE [DbName] GO ALTER ROLE [db_datareader] ADD MEMBER [DbUser] GO DENY SELECT ON [coko].[or_user] TO [DbUser] GO

Also, if required, we will allow data changes, but we will forbid them for the selected object:

 ALTER ROLE [db_datawriter] ADD MEMBER [DbUser] GO DENY UPDATE, INSERT, DELETE ON [coko].[or_user] TO [DbUser] GO

As for the first item (database users without a login):

 USE [Test] GO CREATE USER [LoginlessUser] WITHOUT LOGIN GO

then such users are often used for impersonalization, i.e. perform actions by someone else on behalf of such a user.

For example, you can create a procedure that will be executed on behalf of a specific user and do something that is available only to this user:

 CREATE PROCEDURE DoSomething WITH EXECUTE AS 'LoginlessUser' AS BEGIN SET NOCOUNT ON; -- делаем что-то, что доступно LoginlessUser END

Other users can be given rights to perform this procedure.

You can also switch the execution context in order to execute commands on behalf of someone:

 -- сменим контекст EXECUTE AS USER = 'LoginlessUser'; GO -- делаем что-то от имени LoginlessUser GO -- вернёмся в свой контекст REVERT; GO