set up configurations? I'm having trouble with the specifics:
1. CREATE A USER "WebUser" in current database which uses a Windows Login,
"SERVER\IUSR_XYZ"
2. Give the above user "WebUser" ROLE membership in a database Role names
"WebUsersRole"
3. GRANT Execute permissions to the ROLE "WebUsersRole" for ALL Stored
Procedures in the database which start with the name "HM_"
I've been banging my head over this syntax for many hours and it's making me
NUTS!
Thanks.
AlexAlex Maghen (AlexMaghen@.newsgroup.nospam) writes:
> Hi. Can you please help me with three simple Transact SQL Statements
> that do set up configurations? I'm having trouble with the specifics:
> 1. CREATE A USER "WebUser" in current database which uses a Windows Login,
> "SERVER\IUSR_XYZ"
> 2. Give the above user "WebUser" ROLE membership in a database Role names
> "WebUsersRole"
> 3. GRANT Execute permissions to the ROLE "WebUsersRole" for ALL Stored
> Procedures in the database which start with the name "HM_"
> I've been banging my head over this syntax for many hours and it's
> making me NUTS!
And your professor was so mean that he did not give you hints where in
the manual you should look?
He did not even tell you which version of SQL Server you should use?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland -
Your response is an inapropriate use of this newsgroup. The answers to
*most* of the questions posted in these groups can be found in the
documentation. If I am posting the question here it is because I have tried
to work through the documentation and I am still not succeeding.
If you do not wish to help others in these groups, no problem. Just please
do not respond with "Read the Manual."
Alex
"Erland Sommarskog" wrote:
> Alex Maghen (AlexMaghen@.newsgroup.nospam) writes:
> And your professor was so mean that he did not give you hints where in
> the manual you should look?
> He did not even tell you which version of SQL Server you should use?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Hi Alex
Actually, Erland did not tell you to read the manual. He did express
concern that you had not told us the version of SQL Server you expected
answers for, and you still haven't told us. Erland was also obliquely
expressing his assumption that these questions came from a class assignment,
not from a real business need.
Using these forums for help with homework, without telling us that it is a
homework exercise and that there is no real world environment to help
provide background, IS a very inappropriate use of this newsgroup.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:EBCC71D4-3FB8-4D93-8CB0-C9BD28B1109C@.microsoft.com...[vbcol=seagreen]
> Erland -
> Your response is an inapropriate use of this newsgroup. The answers to
> *most* of the questions posted in these groups can be found in the
> documentation. If I am posting the question here it is because I have
> tried
> to work through the documentation and I am still not succeeding.
> If you do not wish to help others in these groups, no problem. Just please
> do not respond with "Read the Manual."
> Alex
>
> "Erland Sommarskog" wrote:
>|||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.
Alex
"Kalen Delaney" wrote:
> Hi Alex
> Actually, Erland did not tell you to read the manual. He did express
> concern that you had not told us the version of SQL Server you expected
> answers for, and you still haven't told us. Erland was also obliquely
> expressing his assumption that these questions came from a class assignmen
t,
> not from a real business need.
> Using these forums for help with homework, without telling us that it is a
> homework exercise and that there is no real world environment to help
> provide background, IS a very inappropriate use of this newsgroup.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
> news:EBCC71D4-3FB8-4D93-8CB0-C9BD28B1109C@.microsoft.com...
>
>|||Alex Maghen (AlexMaghen@.newsgroup.nospam) 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. :-)
[vbcol=seagreen]
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.)
[vbcol=seagreen]
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.)
[vbcol=seagreen]
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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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 Schem
a
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 themselve
s
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@.newsgroup.nospam) writes:
> 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. :-)
>
> The command is CREATE USER. You need to first to run CREATE LOGIN to creat
e
> 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.)
>
> 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.)
>
> 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@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||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 tex
t
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@.newsgroup.nospam) writes:
> 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. :-)
>
> The command is CREATE USER. You need to first to run CREATE LOGIN to creat
e
> 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.)
>
> 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.)
>
> 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@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Hi Alex
In SQL 2005, when you create an object, you specify the schema that the
object should be placed in. Every user has a default schema, and if you
don't specify a default schema when you add a user, the default schema is
the schema called 'dbo'. It is actually recommended that you always be
specific about schema when you create new objects, so you would just create
each proc as
create proc HM.hm_newproc as
...
There is no way to create the schema to 'dynamically update itself'.
However, you can take a look at DDL triggers. You could have a trigger that
was invoked every time a create procedure is run, and have SQL Server check
the name of the procedure, and if it starts with hm_, it could move it to
the HM schema.
The better choice is just to put the procedure in the correct schema when
you create it.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:5A409CA9-5B26-47FB-962F-814A9F83E2E3@.microsoft.com...[vbcol=seagreen]
> 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:
>|||I have, with your help, figured it all out. I appreciate all the assistance.
Thanks!
"Kalen Delaney" wrote:
> Hi Alex
> In SQL 2005, when you create an object, you specify the schema that the
> object should be placed in. Every user has a default schema, and if you
> don't specify a default schema when you add a user, the default schema is
> the schema called 'dbo'. It is actually recommended that you always be
> specific about schema when you create new objects, so you would just creat
e
> each proc as
> create proc HM.hm_newproc as
> ...
> There is no way to create the schema to 'dynamically update itself'.
> However, you can take a look at DDL triggers. You could have a trigger th
at
> was invoked every time a create procedure is run, and have SQL Server chec
k
> the name of the procedure, and if it starts with hm_, it could move it to
> the HM schema.
> The better choice is just to put the procedure in the correct schema when
> you create it.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
> news:5A409CA9-5B26-47FB-962F-814A9F83E2E3@.microsoft.com...
>
>
No comments:
Post a Comment