Re: 3 Simple Security SQL Statements

Erland -

Thanks for these. #s 1 and 2 are perfect, for 3, I'd like to understand
something: I've never worked with Schemas before and they look like they're
cool. If I create a schema, can it be configured so that even later, when I
add a new Stored Procedure, the database automatically adds it to that Schema
because, say, it is a Stored Procedure starting with "HM_"? Or do I have to
manually add items to the schema each time they are created?

The documentation describing a Schema is pretty light - and pretty confusing
too. But if Schemas can be created so that they dynamically update themselves
based on those kinds of criteria, and I can simply grant permissions to that
Schema for a database role, that seems like the best setup.

Alex



"Erland Sommarskog" wrote:

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

.

Relevant Pages

  • Re: 3 Simple Security SQL Statements
    ... In SQL 2005, when you create an object, you specify the schema that the ... Kalen Delaney, SQL Server MVP ... Schema for a database role, that seems like the best setup. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)
  • Re: fmtonly problem (bug ??)
    ... The stored procedure is simple enough but it uses that function to JOIN ... Using the schema, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Send Adapter
    ... I am trying to use this suggestion without success so far. ... Set the field for the XML part of the SQL insert message to distinguished. ... I am clear on how to create the schema and the ports; ... on how to pass the input XML message to the stored procedure. ...
    (microsoft.public.biztalk.general)
  • Re: SQL Send Adapter
    ... "Alan Smith" wrote: ... > 1 Set the field for the XML part of the SQL insert message to distinguished. ... >> I am clear on how to create the schema and the ports; ... >> on how to pass the input XML message to the stored procedure. ...
    (microsoft.public.biztalk.general)
  • Re: 3 Simple Security SQL Statements
    ... In SQL 2005, when you create an object, you specify the schema that the ... Kalen Delaney, SQL Server MVP ... Schema for a database role, that seems like the best setup. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)