Multi tool use

Disable users of a database not Logins in SQL server 2008

To disable users of a Database in SQL Server 2008, I gave the command:

Use Database
Go
Revoke Connect from username;


It works for the users with SQL Server Authentication such as abcdef.

But not for the user with Windows Authentication such as DomainNameabcdef. It gives the error:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ''.

It does not even work by using the single quotes around the username in the above mentioned command.

Please suggest.

2 Answers
2

It worked when I used the double quotes around the username such as:

use Database
Go
Revoke Connect from "DomainNameabcdef"


To enable users of a database:

Use Database
Go
Grant Connect to "username"


To disable Logins of a server:

Use master
Go
Alter Login loginname disable;


To enable Logins of a server:

Use master
Go
Alter Login loginname enable;


QUOTED_IDENTIFIER ON

[like this]

I identify the disabled users by using the sys.sysusers DMV

you can see on the example below. the result of the query, and further below, navigating through ssms.

select * from sys.sysusers
where islogin =1
and hasdbaccess = 0


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.