Re: 3 Simple Security SQL Statements

Alex Maghen (AlexMaghen@xxxxxxxxxxxxxxxx) writes:
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?

You could run a cursor over the SELECT statement, and then run EXEC() on
every row.

But it was my intention that you should simply copy and paste, but I
forgot to say that.

And I see also that forgot one condition in the WHERE clause:

AND name LIKE 'HM[_]%'

As for getting things into a schema, it would be as simple to change
HM_some_stored_proc to HM.some_stored_proc. That would of course requires
changes to the application code as well, and it's probably not worth it
only to make it a little easier to set permissions.

By the way, Kalen floated the idea to use a DDL trigger to change the
schema. You could also use a DDL trigger to grant rights to WebUserRole
automatically.

--
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: Too Many SET Options
    ... By default its creating lot of 'SET' commands. ... the script to work with an indexed view or an indexed computed column. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: where to get more info on...
    ... SQL Server Books Online is the definitive documentation for these stored ... Apart from that you can look at the source code of these stored ... example when using regular OS commands at a command prompt you can usually ...
    (microsoft.public.sqlserver.server)
  • Re: SQLAgent 2005 SP1 failed to run .bat file.
    ... Create a test file a.txt in E:\firefly. ... Create E:\firefly\test.bat file with the following commands: ... Find the job under SQL Server Agent, ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: How to get modified time of a table
    ... way to get that information using any commands? ... there is no built-in support for this. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • 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)