sql server 2005

[EternalOptimist]

I have a visual basic application, with a sql back end, connecting via odbc.

The back end owner has defaulted to the user who created it (me)
which is fine.

Using windows authentication, which means that anyone can connect and read the data.

How can I make sure that other users(and I dont have any logins or group names yet) are limited to exec only on any stored procedures ?

Please keep it simple, I am not a dba, just a humble developer

[JoJoGabor]

You just need to set the permissions on the tables correctly. Normal users dont need read permissions on a table in order to execute a stored procedure to read that data. So give no rights on the table and exec rights on the stored procedure.

[EternalOptimist]

You just need to set the permissions on the tables correctly. Normal users dont need read permissions on a table in order to execute a stored procedure to read that data. So give no rights on the table and exec rights on the stored procedure.

which users am I giving these rights to ?

[London75]

To do it right, you need to get the application users put into a security group in Active Directory specific to this application and then grant that group access to the server and exec permissions on the stored procedures.

The AD admins will set up the group and the DBA will grant the group permissions. That way, if there are 1st line support people, they can manage application access easily without ever going near the database, in the group has access, take out of the group, no access. Nice and easy.

[EternalOptimist]

To do it right, you need to get the application users put into a security group in Active Directory specific to this application and then grant that group access to the server and exec permissions on the stored procedures.

The AD admins will set up the group and the DBA will grant the group permissions. That way, if there are 1st line support people, they can manage application access easily without ever going near the database, in the group has access, take out of the group, no access. Nice and easy.

excellent, thats the type of answer I was hoping for, pitched nicely at my level.

So if I have an AD group called EOUsers, then I put ten windows logins into that group, what do I have to do in sql server ?
Do I have to set up a user or a group with the same name ?
How do I stop others from execing my sp or seeing my tables ?

Unfortunately I will be expected to act as the dba for this database, and I will be expected to know what I am talking about. To add insult to injury , I am fighting in the dark because I dont have AD on my set up.

[London75]

SQL Server can see that AD group as long as it's part of the same domain.

• So open Management Studio
• Browse to the Security section
• Right click on Logins and select new login
• Make sure the Windows Authentication tick box is selected
• To be sure it's correct, click the Search button (you could just type the group name in the following format DOMAIN\GROUP but typos are easy)
• Find your group by searching AD
• Choose your default database, this should be the database used for your application
• There are two schools of thought for Default Language, some prefer to leave it on default to allow the server config choose language, I only ever dealt with UK so always choose British English as it makes dates reliable.
• Click User Mapping in the left hand pane
• Tick the box next to your database but don't tick any other boxes in the bottom pane as you're not going to give users the ability to select from tables
• Click ok
• ...If that went ok...
• Go back to Management Studio and select the database you are dealing with
• Expand to Security -> Users and double click your group
• Click Securables in the left pane
• Click the add button halfway down
• Select "All objects of the type" and click ok
• Select "Stored Procedures" and click ok
• Here's the laborious bit, you need to click each stored procedure name in the top pane, then click the Grant box in the bottom pane, over and over again until they're all done.
  
  Voila. There is a quicker was to generate permission scripts but sounds like it might be too much for now! If you're interested it involves building a SQL String from the results of the sysobjects table, each stored proc is listed in that table so the right query will generate a grant permissions script which can then be run. Even fancier is a cursor that executes the grant at the same time.
  
  I'd say if you've got more than 40 SPs, a script is worthwhile.

[EternalOptimist]

Sounds like I am adding the user/group to the engine(instance ?), then to the database itself


?

[London75]

Sounds like I am adding the user/group to the engine(instance ?), then to the database itself
?

Exactly, the group has to be granted permission to even access the server instance first, then all the objects it requires.

I'm a bit bored so this is the script to generate the permission script, run it in the database of choice, in text output mode.

select 'grant execute on ' + [name] + ' to ''DOMAIN\GROUPNAME''' from sysobjects where type = 'P' and category <> 2

type='P' shows just procedures and category <> 2 removes some of the default SPs that you're not interested in.

Run the resulting output in a new query window.

[EternalOptimist]

In the database, the user/group was not there. I am guessing that is because the db was not made that user/groups default.


That becomes important because I will have one groups vs 10 databases.


what is the default for my sp viz the new group ? exec or not to exec ?



thanks for the help btw. much appreciated.

[London75]

SQL Server can see that AD group as long as it's part of the same domain.

• So open Management Studio
• Browse to the Security section
• Right click on Logins and select new login
• Make sure the Windows Authentication tick box is selected
• To be sure it's correct, click the Search button (you could just type the group name in the following format DOMAIN\GROUP but typos are easy)
• Find your group by searching AD
• Choose your default database, this should be the database used for your application
• There are two schools of thought for Default Language, some prefer to leave it on default to allow the server config choose language, I only ever dealt with UK so always choose British English as it makes dates reliable.
• Click User Mapping in the left hand pane
• Tick the box next to your database but don't tick any other boxes in the bottom pane as you're not going to give users the ability to select from tables.....Do this for every one of the 10 databases
• Click ok
• ...If that went ok...
• Go back to Management Studio and select the database you are dealing with
• Expand to Security -> Users and double click your group
• Click Securables in the left pane
• Click the add button halfway down
• Select "All objects of the type" and click ok
• Select "Stored Procedures" and click ok
• Here's the laborious bit, you need to click each stored procedure name in the top pane, then click the Grant box in the bottom pane, over and over again until they're all done.
  
  Voila. There is a quicker was to generate permission scripts but sounds like it might be too much for now! If you're interested it involves building a SQL String from the results of the sysobjects table, each stored proc is listed in that table so the right query will generate a grant permissions script which can then be run. Even fancier is a cursor that executes the grant at the same time.
  
  I'd say if you've got more than 40 SPs, a script is worthwhile.

See bolded above, that should sort the database thing. Can I ask why there are 10 databases? That might end up biting you in the ass.