Re: 3 Simple Security SQL Statements

Erland -

One more thing, if I may ask: You last example:

SELECT 'GRANT EXECUTE ON ' + quotename(name) + ' TO WebUserRole'
FROM sys.objects
WHERE type = 'P'
AND schema_name(scbema_id) = 'dbo'

Builds a list of text commands. Forgive me for not knowing how to do this,
but how do I actually get Transact-SQL to "execute" each of those commands?
Is there something like "Evaluate" or something that allows me to pass a text
string of a SQL command inside a stored procedure that SQL Server will then
execute? Otherwise, I don't really undertand how I can use the SELECT
statement you gave me to execute the changes.

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
    ... If you run the SELECT in a query window, it generates the list of commands ... There is procedure called sp_executesql that will take a string and execute ... Kalen Delaney, SQL Server MVP ... Builds a list of text commands. ...
    (microsoft.public.sqlserver.security)
  • Re: 3 Simple Security SQL Statements
    ... Kalen Delaney, SQL Server MVP ... window and execute. ... Builds a list of text commands. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (microsoft.public.sqlserver.security)
  • Re: how to determine who has dba privileges?
    ... Login to SQL Server using Query analyzer and enter your commands. ... If it is MSDE then execute the commands from command prompt ...
    (microsoft.public.sqlserver.security)
  • Application Role and Securityadmin
    ... I've got an application wich uses application roles... ... as well as adding or removing user logins from the SQL Server. ... I have not found a way to abandon the application role in order to execute ... or a way wich I could execute this commands without leaving ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)