How to restrict SELECT permission for sysadmin users... SQL server 2000

Question

How to restrict SELECT permission for sysadmin users... SQL server 2000

venkat_26178

Old Hand

Points: 354
More actions
January 12, 2005 at 8:37 am

#91920

I'm working on some bank application, where in client not instrested even the DBA'S (with Sysadmin) to view some of his table data. i tried with different things but failed in front of SA (Sysadmin) >
Can any one had answer to this - (other then encrypting data) ??? Urgent.
thanks in advance,
venkat
venkat_26178@rediffmail.com

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 1

Any and every SQL Login that has been granted SysAdmin rights (through membership in the SysAdmin server role) has complete, total, and absolute rights, permissions, and access to everything within that instance of SQL Server. I'd be fascinated to hear how this might be blocked.

What your bank needs to do is lock down who has SysAdmin access to their servers. It sounds like they want no one no when no how to have SysAdmin access to the database--a debatable move at best, IF it's even possible. (As soon as something breaks--or even needs to be changed--that requires SysAdmin access, you're dead.)

What they may have to settle for is to identify people they trust--the CEO, the VPs, whoever--and empower them with SysAdmin rights. Then, either they--the trusted people--do all the work, or they delegate the rights on an "only when and only for as long as necessary" basis. Presumably they stand behind them to make sure they're not reading data while optimizing a query or configuring a new backup.

Mild sarcasm aside, there comes a point where People Who Make Decisions have to delegate responsibility and authority--and doing that wisely and competently is why they earn the big bucks. Or so's my take on things.

Philip

venkat_26178 Old Hand Points: 354 More actions · Answer 2

Yeah wht you said is 100% true.... when ur boss ask some thing to do, we need to find the way...

Let me put my question (more clearly) in this way:

I dont want to block Sysadmin user as with out tht we can't do many admin tasks like replication, profiling etc... So other then SA, we are in a plan to create other user with Sysadmin rights, he will be whole responsible for any DBA tasks. The thing is we want to just retrict him to access only some table data (technical words: DENY SELECT ON TABLES).

Can we DENY SELECT PERMISSION TO SYSADMIN (Role) User. ???

Thanks, - venkat

Mauro Ciaccio SSCrazy Points: 2617 More actions · Answer 3

Hi,

If you don't want to pursue the separate server/instance approach, then the only other alternative I know of is to

a) create a custom DB role in the master database [see http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritythedb_executorrole.asp]

b) assign all the necessary permissions to the required database

Hope that helps,

Mauro

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Please refrain from posting the same question multiple times! It's hard to follow for everyone and unnecessary, since most guys here follow all the fora.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Mauro Ciaccio SSCrazy Points: 2617 More actions · Answer 5

Hi Frank,

sorry but I don't understand your comment. Does it refer to the entire thread or just to my reply? If so, could you please specify the link of the other thread where the problem is dealt with?

Thanks

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Mauro,

my post was aimed at the OP. But since the forum software does not support tree view that is a little bit difficult to visualize. Here's the other thread http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=4&messageid=155074

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Though I admit, this question clearly belongs more to security than suggestions.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

gbn SSCertifiable Points: 6429 More actions · Answer 8

Encrypt the data at the column level.

See http://www.activecrypt.com for details on how to do column level encrption.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

I suspect that's not what he wanted to hear.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

venkat_26178 Old Hand Points: 354 More actions · Answer 10

Mauro,

Sorry, if its a user defined DB role, again we cannot perform all admin tasks.

Can any one - list all (Server role) combination = Sysadmin server role

i mean (serveradmin + setup admin + soooooo ) = Sysadim role.

thanks - venkat.

EdVassie SSC Guru Points: 60472 More actions · Answer 11

As a few people have said, in SQL Server it is not possible to restrict what anyone with Sysadmin authority can do. SQL Server simply does not check if a Sysadmin has or does not have rights to do something - it just allows access.

If you want to prevent someone with Sysadmin looking at your data, then you have to use a different DBMS (not SQL Server!!) that allows Sysadmin rights to be limited.

Alternatively in SQL Server you can prevent a Sysadmin from making any sense of your data by encrypting it. This does not stop a Sysadmin adding, changing or deleting the data, it just means they have no idea what the data represents.

Those are the choices. Do not expect SQL2005 to change them.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 12

Actually it's not so much sysadmin authority. The real level this hits home is at dbo. If a user is dbo within a database, no security applies. Since members of the sysadmin role map in as dbo, you get the same result. That seems like a nit-picky distinction but it's a crucial one. That means if the database is owned by someone NOT in the sysadmin role, they too avoid security. Hence the reason it's necessary to encrypt at the application level.

K. Brian Kelley
@kbriankelley

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 13

in addition to the other replies.

You can also try to disencourage native db-access by using your own naming-conventions.

I have one db that has tables named A0001,.. with columns C0001, C0002,...

(select C0452, C0825 from A0125 where C0864 = ...)

I can assure you that these type of tables/views are no fun at all to access natively.

Schema are being managed using ERD-tools.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

Now that reminds me of http://mindprod.com/unmain.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]