Re: 3 Simple Security SQL Statements
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sat, 23 Jun 2007 21:24:05 +0000 (UTC)
Alex Maghen (AlexMaghen@xxxxxxxxxxxxxxxx) writes:
Okay, let's take a deep breath. First of all, sorry if I overreacted. Just
really didn't like the tone. Second, SQL Server 2005. Third, oh, my
friends, if *only* I were still in school! :) It may *sound* like a class
question, but it is, in reality, something I need in order to simply the
process of publishing an entire database file from one system to another
on a repeated basis during the development cycle. Each time I do, I need
to reset a bunch of settings that it is impossible for me to have in
place on the development machine, but which are required on the staging
server. Until now I have been doing this all manually in the Management
Studio and it's making me nuts.
And, onbliquely or not, he *did*, in fact, tell me to read the manual. :)
OK, lets look at your questions again, now that we know which version of
SQL Server you are working with. This is necessary to know for the first
question, and it has an implication for the third as well.
I'm sorry if I took your questions for a class assignment. Maybe you
are so fresh out of school, that you keep asking questions that way. :-)
1. CREATE A USER "WebUser" in current database which uses a
Windows Login, "SERVER\IUSR_XYZ"
The command is CREATE USER. You need to first to run CREATE LOGIN to create
the login.
CREATE LOGIN [SERVER\IUSR_XYZ] FROM WINDOWS;
go
USE yourdb
go
CREATE USER WebUser FROM [SERVER\IUSR_XYZ]
I types these commands from memory. If there are syntax errors, please
check in Books Online. (Yes, I'm referring you to the manual again. But
either you look in the manual or I do. And you are the one who has the
problem.)
2. Give the above user "WebUser" ROLE membership in a database
Role names "WebUsersRole"
EXEC sp_addrolemember 'WebUsersRole', 'WebUser'
If this fails, swap the paramerers. (There are two addrole procedures,
one for database roles and one for server roles, and they have their
two parameters in different order.)
3. GRANT Execute permissions to the ROLE "WebUsersRole" for ALL
Stored Procedures in the database which start with the name "HM_"
Had you had a schema HM, it would have been easy.
GRANT EXECUTE ON SCHEMA::HM TO WebUserRole
Now you need to grant right for every procedure, and if new procedures are
added, WebUserRole will not automatically get the right to execute these.
A simlpe way to do this is:
SELECT 'GRANT EXECUTE ON ' + quotename(name) + ' TO WebUserRole'
FROM sys.objects
WHERE type = 'P'
AND schema_name(scbema_id) = 'dbo'
I've here assumed that all procedures are in the dbo schema, and that
you don't have stored procedures written in the CLR. In that case, you
need to add one more type, which I think is PA, but you should look it
up in Books Online under sys.objects to make sure.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- Re: 3 Simple Security SQL Statements
- From: Alex Maghen
- Re: 3 Simple Security SQL Statements
- From: Alex Maghen
- Re: 3 Simple Security SQL Statements
- References:
- Re: 3 Simple Security SQL Statements
- From: Erland Sommarskog
- Re: 3 Simple Security SQL Statements
- From: Kalen Delaney
- Re: 3 Simple Security SQL Statements
- From: Alex Maghen
- Re: 3 Simple Security SQL Statements
- Prev by Date: Re: 3 Simple Security SQL Statements
- Next by Date: Re: Cross VPN Domain Authentication
- Previous by thread: Re: 3 Simple Security SQL Statements
- Next by thread: Re: 3 Simple Security SQL Statements
- Index(es):
Relevant Pages
|
|
