SQL SERVER 2008 database authorization

You can grant/revoke the select/update/delete privileges to the tables. Read
https://msdn.microsoft.com/en-us/library/ms187719.aspx?f=255&MSPPError=-2147217396

thanks for the feedback. I want to not allow the users to open the database

For entire sql server instance.

1. You can deny to logins to connect to SQL server. A user will not be able to connect to database engine if you set the deny for the login.

For database level.

You can remove the user and login mapping from database. Unmapped login would not be able to access the database.

Best option: dont create any individual logins into the server without any specific case.
always use Windows local group or AD group and all all logins into that groups.
So you can grant access permissions to whole group accordingly.

i dont want to go into windows group discussion. i want to deny user with proper credential to log into studio management. there is got to be some way. somebody help me.

For starters, ensure only administrators have sysadmin rights in SQL.  Then look for each database, take a look at logins and if you have domain users then remove it and only assign AD accounts for users who need access to the database.

According to your requirement here is the script.

You can deny  non Sysadmin logins only

1.      Run this select statement in query window and copy the result of first column [DenyWindosLogins]. This will generate deny script for non sysadmin logins. For example : Deny connect sql to [ABC\x12343]

2.      Copy the result showing in first column [DenyWindosLogins] and paste and execute it in query window.

select 'Deny connect sql to ['+name+']' as DenyWindosLogins, name, hasaccess, isntname, isntuser, isntgroup, sysadmin from                           sys.syslogins
where isntuser=1 and name !='NT AUTHORITY\SYSTEM' and sysadmin=0

--Sample script Deny connect sql to [ABC\x12343]


You can not deny sysadmin logins. But you can disable them, here is the script.
For example : Alter login [Abc\x12343] disable


select 'Alter login  ['+name+']  Disable' as DenyWindosLogins, name, hasaccess, isntname, isntuser, isntgroup, sysadmin from sys.syslogins
where isntuser=1 and name !='NT AUTHORITY\SYSTEM' and sysadmin=1

Maybe is better to clarify that SQL Server Management Studio (SSMS) is not SQL Server engine.
SSMS is only an interface to help administering SQL Server. If you want to deny users to see databases you need to revoke their access to the database so when they use SSMS or another database tool they can't see and connect to databases.

Victor,  you see the users connect to the computer with administrator access and when they open the SSMS they have access to the databases. I want them not to be able to see the databases.

Can you post those users (you can apply a mask in their names) and their server and database roles?

Victor

I posted the screenshot of the user. this user(test) is required by my java application.

deepakChauhan

Can you tell me what this query will do exactly. First i want to test on my machine before doing it on the production db,can you also let me know how can i again get those logins back.

Victor
sorry forgot to press the upload button
2015-05-09-8-52-12.gif

Thanks for your feedback.

Q; which sql version you are using ?  screenshot shot showing it shuld be sql server 2008, Is this your production server.

I am using sql server 2008. yes

Q: is there any ComputerName\Administrators group added in SQL logins? In the screenshot this is not showing.

what do you mean by -- in SQL logins. you see when i install sql server i go for mixed authentication.

so to disable this i need to do this Alter login [ZAJ-VAIO\ZAJ] disable.correct??

Yes it is correct

to disable > Alter login Alter login [ZAJ-VAIO\ZAJ]  disable

to enable >  Alter login Alter login [ZAJ-VAIO\ZAJ]  enable