• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 109
  • Last Modified:

SQL SERVER 2008 database authorization

Hello there,

I have a sql server 2008 which is installed on my production server(server 2003). Different people have access to the server and hence can open the database also. I want to know is there some way that i can lock the Management studio,so everybody dont have access. at present anybody can open the managemet studio and browse the databases and its tables. I have attached the screenshot which the user sees when it opens the management studio.

cheers
ZOlf
ee.gif
0
zolf
Asked:
zolf
  • 8
  • 4
  • 2
  • +2
1 Solution
 
HuaMin ChenSystem AnalystCommented:
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
0
 
zolfAuthor Commented:
thanks for the feedback. I want to not allow the users to open the database
0
 
Deepak ChauhanSQL Server DBACommented:
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.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
zolfAuthor Commented:
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.
0
 
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
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.
0
 
Deepak ChauhanSQL Server DBACommented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
zolfAuthor Commented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post those users (you can apply a mask in their names) and their server and database roles?
0
 
zolfAuthor Commented:
Victor

I posted the screenshot of the user. this user(test) is required by my java application.
0
 
zolfAuthor Commented:
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.
0
 
zolfAuthor Commented:
Victor
sorry forgot to press the upload button
2015-05-09-8-52-12.gif
0
 
Deepak ChauhanSQL Server DBACommented:
@Can you tell me what this query will do exactly. @ how can i again get those logins back
you can get the logins back by granting the connect permission or enable the disabled logins in sql server.

For example: user Test showing in screenshot. let disable this login.
 for disable >   Alter login Test disable.
to get this back > Alter login Test enable.

to deny connect permission >> Deny connect SQL to Test
To get it back > Grant connect SQL to Test.
==============================================================================================
Select command to generate the Deny script for all windows logins which are added in SQL server and don't have sysadmin access.

1 .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  >> Deny connect sql to [ABC\x12343]  - this will deny the login ABC\x12343 to connect to SQL server.

you can grant connect permission again >> Grant connect sql to [ABC\x12343]


However you can not deny sysadmin logins but you can disable them.

2. 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

For example : Alter login [Abc\x12343] disable  -- this will disable the [Abc\x12343] login

You can enable the login again just reverse the script >> Alter login [Abc\x12343] enable
==============================================================================================
Q; which sql version you are using ?  screenshot shot showing it shuld be sql server 2008, Is this your production server.

Q: is there any ComputerName\Administrators group added in SQL logins? In the screenshot this is not showing.
0
 
zolfAuthor Commented:
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.
0
 
zolfAuthor Commented:
so to disable this i need to do this Alter login [ZAJ-VAIO\ZAJ] disable.correct??

z
0
 
Deepak ChauhanSQL Server DBACommented:
Yes it is correct

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

to enable >  Alter login Alter login [ZAJ-VAIO\ZAJ]  enable
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 8
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now