I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!alvinstraight38@.hotmail.com wrote:
Quote:
Originally Posted by
Hey guys,
>
I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!
>
if you right click on the SP and click properties, that should bring up
the options.
--sharif|||alvinstraight38@.hotmail.com (alvinstraight38@.hotmail.com) writes:
Quote:
Originally Posted by
I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!
If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.
Then again, in the long run you are better of using GRANT commands.
--
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|||On Sep 20, 4:11 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
alvinstraigh...@.hotmail.com (alvinstraigh...@.hotmail.com) writes:
Quote:
Originally Posted by
I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!
>
If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.
>
Then again, in the long run you are better of using GRANT commands.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
Microsoft SQL Server Management Studio Express Version 9.00.2047.00|||alvinstraight38@.hotmail.com (alvinstraight38@.hotmail.com) writes:
Quote:
Originally Posted by
Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
>
Microsoft SQL Server Management Studio Express Version 9.00.2047.00
That's SP1.
--
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|||On Sep 20, 10:26 pm, "alvinstraigh...@.hotmail.com"
<alvinstraigh...@.hotmail.comwrote:
Quote:
Originally Posted by
On Sep 20, 4:11 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>
>
>
Quote:
Originally Posted by
alvinstraigh...@.hotmail.com (alvinstraigh...@.hotmail.com) writes:
Quote:
Originally Posted by
I'm pretty new to SQL configuration, and I need to give EXECUTE
persmissions for one of the SQL user roles. I am running SQL 2005
Management Studio Express - free version. I found the list of my
stored procedures, but I can not locate any permissions screen. Can
someone help point me in the right direction? Thanks!
>
Quote:
Originally Posted by
If you want to use the GUI, make sure that you have SP2. I think that
alternative was missing in RTM and SP1.
>
Quote:
Originally Posted by
Then again, in the long run you are better of using GRANT commands.
>
Quote:
Originally Posted by
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Quote:
Originally Posted by
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
Ahh, I see now why I was lost. I right click on the SP, and there is
no option for Properties. Yet, I can set permissions on tables. How
stupid. You mention SP2. How can I tell which service pack I am
running? I went to Help - About and it shows:
>
Microsoft SQL Server Management Studio Express Version 9.00.2047.00- Hide quoted text -
>
- Show quoted text -
You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')|||abu hisham wrote:
Quote:
Originally Posted by
You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
>
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')
To clarify, this will not directly grant the permissions, but will
output SQL code that can be copy+pasted into Query Analyzer and
executed to grant the permissions.|||abu hisham (yjogee@.hotmail.co.uk) writes:
Quote:
Originally Posted by
You can grant permissions dynamically in this way to all db objects:
/* tables and views*/
select 'Grant select,insert,update,delete on '+name+ ' to USER'
from sysobjects
where xtype in ('U','V')
>
/*Stored procedures*/
select 'Grant exec on '+name+ ' to USER'
from sysobjects
where xtype in ('P')
In SQL 2005 this can be achieved with a single statement:
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON SCHEMA::schema_name TO user
Access granted on schema level are inherited by objects in the schema, which
means that it also applies to future objects.
--
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
No comments:
Post a Comment