sql server 2005

[EternalOptimist]

The reason I was worried is because you'll have to maintain things like stored procedures and users in each database making code control harder. It just means you have to be really careful to make sure everything is always up to date.

There is one tip that isn't necessarily approved of but works, create your stored procedures in the master database and start the name sp_. they then don't need to be created in all other databases as running db1.dbo.sp_storedproc1 will run the procedure sp_storedproc1 in the context of the db1 database even though it actually lives in master.

it will come as no suprise to you to know that I want as little to do with the (ultimate) clients dba's as is possible. So putting stuff in master is a good tip, but I probably wont follow it up

[London75]

I am converting a software house from vb/access to sql server. They have a pretty granular set up. This particular target company has ten of their products.

I have suggested merging 10 into 2 - we'll see what occurs.

The reason I was worried is because you'll have to maintain things like stored procedures and users in each database making code control harder. It just means you have to be really careful to make sure everything is always up to date.

There is one tip that isn't necessarily approved of but works, create your stored procedures in the master database and start the name sp_. they then don't need to be created in all other databases as running db1.dbo.sp_storedproc1 will run the procedure sp_storedproc1 in the context of the db1 database even though it actually lives in master.

[EternalOptimist]

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.

I am converting a software house from vb/access to sql server. They have a pretty granular set up. This particular target company has ten of their products.

I have suggested merging 10 into 2 - we'll see what occurs.

[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.

[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]

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]

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


?

[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]

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]

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]

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 ?

[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]

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