Re: 3 Simple Security SQL Statements

Alex

Sorry, I hadn't finished my coffee yet, and my mind was not completely in
gear.

The procedure I meant to reference was xp_executeresultset that executed the
RESULT of a query, which is what you would need to actually run the GRANT
statements generated by the SELECT.

Unfortunately, xp_executeresultset was in 2000 and is not available in 2005
(it was undocumented, so MS is under no obligation to provide ongoing
support)...

Fortunately, MVP Adam Machanic has created a replacement which you can read
about here:

http://sqlblog.com/blogs/adam_machanic/archive/2006/10/19/replacing-xp-execresultset-in-sql-server-2005.aspx


--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com


"Kalen Delaney" <replies@xxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ejNmjLotHHA.3356@xxxxxxxxxxxxxxxxxxxxxxx
Hi Alex

If you run the SELECT in a query window, it generates the list of commands
in the result window. You then would copy and paste it back to the query
window and execute.

There is procedure called sp_executesql that will take a string and
execute it. You can try reading about that and see if it works for you.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com


"Alex Maghen" <AlexMaghen@xxxxxxxxxxxxxxxx> wrote in message
news:405CFA89-6C80-4EFD-87D4-8622F94D024C@xxxxxxxxxxxxxxxx
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
    ... Builds a list of text commands. ... but how do I actually get Transact-SQL to "execute" each of those commands? ... Second, SQL Server 2005. ... EXEC sp_addrolemember 'WebUsersRole', 'WebUser' ...
    (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)
  • Re: SSIS - OLE DB Command - how to retrieve query results ???
    ... my side according to your process, and I managed to execute the task. ... Data Destination: <My SQL Server 2005 Instance>.TestDB ... OLE DB Command: ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Error EXCEPTION_ACCESS_VIOLATION
    ... >>We have some DTS jobs that runs every night called via a ... >>Package ... If this is no help, then please describe how you execute the package, ... DTS - http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals ...
    (microsoft.public.sqlserver.dts)