Setting Appropriate Permissions

[faccorp]

Hello everyone,

I get a bit confused with setting permissions for my database. I am using Windows Authentication and a C++ front end app is connecting to a SQL 2005 database. Basically, in my front end, the initial user (who must be an Administrator of the connecting computer), creates a database on the server by attaching an 'empty shell' of the entire database. Then, this user is added as a login and as a user of the newly attached database.

I'm confused on whether or not I should make this user a sysadmin user. They don't need to be, but since the user has Administrative rights on the computer, and we are connecting using Windows Authentication, then doesn't that give them the ability to go in and make themselves a sysadmin using SQL Server Management Studio.

The bottom line is that I want each user to have the following rights: sp execute, create and alter views, and backup database. I do this when I create the user (using GRANT ...). I don't want to give them the ability to manually open tables and change table structures or change table data.


Thanks for any feedback.

 

[JBaileys]

Seems unusual that the C++ app creates the db. But, I don't want to question your approach - but you may want to think about it.

You said: The bottom line is that I want each user to have the following rights: sp execute, create and alter views, and backup database.

So, can I assume you have 2 sets of users. Administrators that have rights to create dbs and other users with sp execute, create / alter views and backup. How are these users created? Do the administrators that create the db also create the users? If someone creates a backup can they also execute a restore?

There is a way to remove windows administrators from sa rights. It is not recommended.

 

[ptheriault]

There are server rols that you can use.
like dbcreator, securityadmin.

If an account creates a db then that accounts owns the db and can do anything they wish to it.

sysadmin would be over kill.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Of course you realize that none of this matters if you leave the built-in admin account on the server. By placing you C++ account in the server admin group it will map to the 'sa' login by built=in admin.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[faccorp]

Thanks for the reply's.

Here is some more information. I want the user to install the app. When the app runs for the first time it copies the shell of the database to the SQL Data folder and attaches it.

After that, I don't want the user to be able to get into the database unless they're accessing it via the application.

I know I can remove SELECT, UPDATE, DELETE rights, encrypt my stored procedures, and then just give them EXECUTE rights and ability to create and modify views. But can't an adminstrator of the computer go in and change that if they want??


I had a previous system that used Access as the database and I just password proteced it so that I was the only person that could go in and view the tables and queries. I'm trying to so a similar thing with SQL.

 

[ptheriault]

The server admin can go in and change it if you leave the built-in admin account on the server. If you remove the built-in admin account from SQL you effectivly separate server admin from SQL admin.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[faccorp]

Paul,

Thank you. I will look into this. Ideally, I would like to create some other type of admin login/user that only I would know. Then, for maintenence purposes I could login to the server and make any necessary changes.

 

[JBaileys]

Faccorp,

Do you think you have a lack of trust? I know the feeling. One recommendation is to have multiple people that can support the "maintenance" - just in case you go off to a new job.

JB