Restrict Access to the C: Drive

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Restrict Access to the C: Drive

Author

Topic

jmenke
Starting Member

7 Posts

Posted - 2008-03-25 : 16:28:50

My users have sa rights to our SQL servers. I want to restrict their access to the C:\ so that they do not restore DB's there.

I thought at first I could create a Windows user that runs the SQL Service then grant them read rights to the C:\. This does not give the user enough rights to start the service.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-25 : 18:34:05

You can't restrict access to a sysadmin.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-25 : 23:19:32

Why give user sa right at first place?

jmenke
Starting Member

7 Posts

Posted - 2008-03-26 : 14:02:48

quote:
Originally posted by rmiao

Why give user sa right at first place?

This will give the users the ability to restore DB's regardless of its source. You may not be a member of the DB you are restoring.

jmenke
Starting Member

7 Posts

Posted - 2008-03-26 : 14:19:28

quote:
Originally posted by tkizer

You can't restrict access to a sysadmin.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

I am not trying to restrict sa access. I am trying to restrict the windows user running the SQL Server Service from restoring to the c: drive.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-26 : 14:29:17

Educate your users?
If they are clever enough to get SA privileges, they certainly know how to avoid the c: drive, right?

E 12°55'05.25"
N 56°04'39.16"

jmenke
Starting Member

7 Posts

Posted - 2008-03-26 : 14:35:04

quote:
Originally posted by Peso

Educate your users?
If they are clever enough to get SA privileges, they certainly know how to avoid the c: drive, right?

I'd rather not take the wishful thinking approach. A problem I have found with education, it is as good as the people that are present at that moment.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-26 : 16:55:22

quote:
Originally posted by jmenke

quote:
Originally posted by tkizer

You can't restrict access to a sysadmin.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

I am not trying to restrict sa access. I am trying to restrict the windows user running the SQL Server Service from restoring to the c: drive.

That is what I am trying to tell you. You won't be able to restrict the access to the C drive.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-26 : 22:37:28

Restoring db doesn't need sa permission, and you can set default db file path to other drive than c:\ in sql server properties.

jmenke
Starting Member

7 Posts

Posted - 2008-03-27 : 08:43:32

quote:
Originally posted by rmiao

Restoring db doesn't need sa permission, and you can set default db file path to other drive than c:\ in sql server properties.

I am finding that restoring a db from a different SQL Server does require sa. if you restore a db from a different sql server that does not have your user in it with the same UID then you will not have access.

I agree that you can set the default path to a different drive but again, that is wishful thinking. restriction is the key.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-27 : 08:57:41

if you want help, you should step back and clearly state exactly what your workflow/process is, and exactly what you are trying to do. it is hard to give you the best solution without a clear understanding of what your users are doing, why they are having trouble somehow with these restores, and so on. I suspect that there is probably a much easier way to accomplish what you need, but we can't know for sure without the details.

- Jeff
http://weblogs.sqlteam.com/JeffS

jmenke
Starting Member

7 Posts

Posted - 2008-03-27 : 10:13:13

quote:
Originally posted by jsmith8858

if you want help, you should step back and clearly state exactly what your workflow/process is, and exactly what you are trying to do. it is hard to give you the best solution without a clear understanding of what your users are doing, why they are having trouble somehow with these restores, and so on. I suspect that there is probably a much easier way to accomplish what you need, but we can't know for sure without the details.

I will try sum up my request regardless of my users workflow/process.

I want to prevent SQL Server from writing to specific drives. I want the ability to control where SQL Server can write to. I want to grant SQL Server access to the E:\ and thats it.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-27 : 10:37:09

OK, we offered to help, oh well. Good luck.

- Jeff
http://weblogs.sqlteam.com/JeffS

jmenke
Starting Member

7 Posts

Posted - 2008-03-27 : 10:43:51