January 7, 2008 at 9:37 am
Hi,
I'm trying to set up an internet project, but have little experience of SqlServer. I've read Microsofts SqlServer DBA Survival Guide, but am still a little confused. I'd appreciate your valuable help here.
Apologies if any of this seems dumb.......
I've installed SqlServer 2005 and created a Database called 'Horace'. The owner of this database is 'sa'. I've created 50 tables in the database and code that interacts with these tables.
Users will log onto the database remotely and be able to select, update, insert and delete from some of these tables.
I believe I need a user-defined database role to tie down access.
How do I create this role, how do I add users to it, and how do i tie down the appropriate permissions?
Please resist the temptation to flame, I need advise.
Many thanks.
January 7, 2008 at 9:47 am
This sounds like a job for...duh duh duh dah...SCHEMAS! Check your SBO for schemas [SQL Server] and read up a bit on schemas and how to create/manage them. To me it sounds like what you need. Schemas will allow you to create different views for your users and gives you the flexibility to lock down certain tables/accesses depending on what they need.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
January 7, 2008 at 9:50 am
When u create a database user u can give that user certain roles like db_owner ,db_ddladmin etc.These roles are fixed and u cannot add any more roles to that,out of these roles u can tick those u want fot that particular user.
January 7, 2008 at 11:20 am
thanks.
I've looked at the standard database user roles, and they are not bespoke enough.
The system will have hundreds of users, so I need to create a role with the correct permissions, then just add new users to the role. How do I create such a bespoke role?
January 7, 2008 at 11:28 am
Books Online has an article on creating roles on a server:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/b0cd54ad-e81d-4d71-acec-8a6d7261ca08.htm
That should give you the data you need on how to do this. It's pretty simple.
(If you use domain security instead of SQL security, you need to create the role in the domain and then add it to the security of the server and so on from there.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2008 at 2:44 pm
thanks for the advice.
Had a rethink how I want to go about this. As SA owns the database, I want to create a new single user, then get SA to grant specific permissions to that user. This new user will require 'select', 'update' and 'insert' permissions, but not 'delete'.
So.....
I create a new user in Management Studio Security/Logins called 'ServiceUser'
In the Login Properties, this user has a default profile of 'Public
In the User Mapping, I select database 'Horace'
Questions
So that's my new user created, how do I go about locking the permissions down for that new user in Management Studio? I'm guessing I need to create a script as SA with, for example 'grant select on TableA to ServiceUser;' but this is the bit im unsure about
January 8, 2008 at 3:20 am
Is this the best way of going about this?
January 8, 2008 at 6:10 am
It depends on what you are planning to do with the user that was created. The best way would be to add the user to a role and then give access to the role to stored procs. That would mean no T-SQL statements can be executed using this user. The user can only execute stored procs.
Another thing you can do is add this user to the role Data_reader. This gives the user select permission.
Or you can grant select to the objects that is needed.
-Roy
January 8, 2008 at 7:40 am
In Management Studio, open up the database, open up the Security branch, open Users, right-click the user you want to edit permissions for, open the Securables tab, use Add to grant permissions on tables, procs, functions, etc. That or a script. Either one works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2008 at 9:04 am
Thanks again for your input.
If I go Server/Security/Logins
then right click on the ServiceAccount (that I created earlier), then select Properties.
I select the Securables Page, and click on the 'Add' button
It then asks me 'What objects do you wish to add?' but only gives me options of object types for Servers, Endpoints and Logins.
I want to select tables in the Midas Database, but I can't get to them this way.
Help!
January 8, 2008 at 10:18 am
You probably did not give access for this user to Midas DB. Add this user Public rights to the Midas DB.
Once you have done that, in the DB node (Midas), there is a Security/Users as well. There you should be able to see this user. You can right click on the user and take properties and do what ever you want.
Or you can just give permission to the ServiceUSer to Midas DB as just Public and Open up a New Query window and give permission using T-SQL.
Grant Select on to ServiceUser.
-Roy
January 8, 2008 at 1:14 pm
Thanks, I'm making progress. I can now get a list of the tables (50 of them), but I dont want to click 150 times for insert, update, and select privileges.
How do I script this dynamically?
In Oracle I would
select 'grant select on '||table_name||' to serviceuser;'
from user_tables;
Not sure how to do this using Management Studio
January 8, 2008 at 1:26 pm
horace (1/8/2008)
Thanks, I'm making progress. I can now get a list of the tables (50 of them), but I dont want to click 150 times for insert, update, and select privileges.How do I script this dynamically?
In Oracle I would
select 'grant select on '||table_name||' to serviceuser;'
from user_tables;
Not sure how to do this using Management Studio
You can script it using GRANT command:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a760c16a-4d2d-43f2-be81-ae9315f38185.htm
Simplified syntax for GRANT
GRANT { ALL [ PRIVILEGES ] }
| permission [ ( column [ ,...n ] ) ] [ ,...n ]
[ ON [ class :: ] securable ] TO principal [ ,...n ]
[ WITH GRANT OPTION ] [ AS principal ]
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
January 8, 2008 at 1:29 pm
USE MIDAS;
Go
select 'grant select on ' + name + ' to serviceuser' from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1
GO
I think that should work
-Roy
January 8, 2008 at 4:36 pm
thanks guys, great advice and help.
is there a spool command in sqlserver? Im not being lazy, it's just that I find BOL so un-intuitive right now being an (ex) oracle chap
in oracle it would be
spool c:\my_dynamic_sql.txt
sql statement;
spool off
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply