December 10, 2014 at 10:20 pm
Hi,
We have a login with windows authentication. That login has sysadmin and public server roles. But We didn't map that login to some user databases. For some databases we mapped and gave dbowner.
Suppose we have databases Test1, Test2, Test3. The login name is TestLogin and the Testlogin has sysadmin serverrole. Mapped to Test2 and Test3 databases and has dbowner database roles.
Then are they able to connect to the Test1 or not?
In that case this Test login can able to restart the Sql server
December 11, 2014 at 6:45 am
ramana3327 (12/10/2014)
Hi,We have a login with windows authentication. That login has sysadmin and public server roles. But We didn't map that login to some user databases.
There is your answer right there. Sysadmin logins can do ANYTHING in SQL Server, regardless of whether they are mapped to a database or not.
December 11, 2014 at 12:55 pm
What about the test1 database?
Are they able to access the data from the test1 database?
December 11, 2014 at 1:02 pm
Brandie Tarvin (12/11/2014)
ramana3327 (12/10/2014)
Hi,We have a login with windows authentication. That login has sysadmin and public server roles. But We didn't map that login to some user databases.
There is your answer right there. Sysadmin logins can do ANYTHING in SQL Server, regardless of whether they are mapped to a database or not.
December 11, 2014 at 1:53 pm
I am re-framing my requirement.
We have a team working as DBA (Offshore). We have 5 user databases. One database has the secure data and doesn't to reveal the data to offshore team.
How to give that kind of permissions.
They need to do their full DBA role for the remaining databases other than that one database.
December 11, 2014 at 2:16 pm
You do that by putting said database on a different server with different security.
SA can change anything, including their own security rights. You can explicitly deny, then can revoke it. You can block access with a trigger, they can turn it off.
SA is god in a server. You have given away godmode.
Move the protected data somewhere else, or restrict their duties.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 11, 2014 at 2:41 pm
ramana3327 (12/11/2014)
I am re-framing my requirement.We have a team working as DBA (Offshore). We have 5 user databases. One database has the secure data and doesn't to reveal the data to offshore team.
How to give that kind of permissions.
They need to do their full DBA role for the remaining databases other than that one database.
First, remove sa :-).
Then give them db_owner permission on all the other dbs. Don't give them any permission to your db.
They will not be able to do certain system-level things in SQL itself, but they will full access and control of the other dbs and their objects.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 11, 2014 at 9:47 pm
SA account is already disabled. so I need to give public server role and map as dbowner for remaining databases right?
December 12, 2014 at 5:20 am
ramana3327 (12/11/2014)
SA account is already disabled. so I need to give public server role and map as dbowner for remaining databases right?
Yes. That will prevent them from accessing server level items that they don't need access to or other databases they shouldn't see.
December 12, 2014 at 8:29 am
Brandie Tarvin (12/12/2014)
ramana3327 (12/11/2014)
SA account is already disabled. so I need to give public server role and map as dbowner for remaining databases right?Yes. That will prevent them from accessing server level items that they don't need access to or other databases they shouldn't see.
I certainly would not give public full permissions on any db!
I presumed they already had their own logins that you could add to the instance and add permissions for. If not, create specific logins and give them permissions.
You need to give them "db_owner" permissions, but they don't need to be -- and in my view shouldn't be -- the true "dbo" of any db.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 13, 2014 at 7:27 am
We created a seperate login. And gave db_owner permissions for the remaining databases. But my doubt is about the server level permission.
We are assigned public server role to that login. Is it fine or do we need to give high previliages?
What about the system databases mapping. Do I need to map sys databases and give dbowner for system databases also.
Just I need to avoid one database access for them.
December 15, 2014 at 6:02 am
ramana3327 (12/13/2014)
We created a seperate login. And gave db_owner permissions for the remaining databases. But my doubt is about the server level permission.We are assigned public server role to that login. Is it fine or do we need to give high previliages?
Okay, at this point, I suggest you find Books Online and read up on security. Because it is obvious to me you haven't been reading our responses. Why would you give high level priviledges (server level permissions above public) to people that you only want to give database access to?
What about the system databases mapping. Do I need to map sys databases and give dbowner for system databases also.
What access do they need to the system databases? Why would they need access to the system databases?
Go read up on SQL Security. Go read up on the database roles and the server roles. DO NOT DO ANYTHING with your server security until you understand the basics.
December 15, 2014 at 7:15 am
Here my concern is if I gave public server role and dbo owner for some databases, they can't see job history or they can't create maintenance plans
December 15, 2014 at 10:12 am
You can give them separate permissions in msdb to deal with jobs.
I can't imagine why you would want people arbitrarily creating maintenance plans, but you should be able to add that permission in msdb as well.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 15, 2014 at 2:36 pm
ramana3327 (12/15/2014)
Here my concern is if I gave public server role and dbo owner for some databases, they can't see job history or they can't create maintenance plans
Well, either you're an admin or you're not, usually. If they're not an admin, they don't get to play in jobs.
However, there's a mixed solution, where they can create and modify their own jobs and are read only for any others. Mostly because you can't give a job a group as an owner in 2k8. That's without giving away sa.
Job history can be seen by this same role. These are the SQLAgent<action>Roles in MSDB.
You really need to go sit down with a security manual and read through everything available, if for no other reason than to know what types of options are out there. It won't take you too long and from there you can lookup what you need to fine tune the specifics. If you get stuck, just ask, but you need to get a grounding in the process before we can help you nail it down to exactly what you want. Most of your needs should be pretty straight forward.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply