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
zolfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.