Changing Permissions of a Stored Procedure

Can I programmatically change/set the permissions of a stored procedure, and
if so how would I do this?
I am creating several and am fed up having to go into EM to change
permissions.
Thanks
Keith
DENY CREATE PROCEDURE TO Username
or
You can make him,/her to be member of some fixed database roles (for more
details please refer to BOL)
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>
|||Hi,
Use the GRANT Stateemnt from Query ANalyzer.
GRANT EXEC on proc_name to <user_name/role_name>
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>

Changing Permissions of a Stored Procedure

Can I programmatically change/set the permissions of a stored procedure, and
if so how would I do this?
I am creating several and am fed up having to go into EM to change
permissions.
ThanksKeith
DENY CREATE PROCEDURE TO Username
or
You can make him,/her to be member of some fixed database roles (for more
details please refer to BOL)
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>|||Hi,
Use the GRANT Stateemnt from Query ANalyzer.
GRANT EXEC on proc_name to <user_name/role_name>
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>

Changing Permissions of a Stored Procedure

Can I programmatically change/set the permissions of a stored procedure, and
if so how would I do this?
I am creating several and am fed up having to go into EM to change
permissions.
ThanksKeith
DENY CREATE PROCEDURE TO Username
or
You can make him,/her to be member of some fixed database roles (for more
details please refer to BOL)
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>|||Hi,
Use the GRANT Stateemnt from Query ANalyzer.
GRANT EXEC on proc_name to <user_name/role_name>
--
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uLKobuDWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> Can I programmatically change/set the permissions of a stored procedure,
and
> if so how would I do this?
> I am creating several and am fed up having to go into EM to change
> permissions.
> Thanks
>

Changing Permissions for all Stored Procs in a SQL2005 DB

I have a massive number of Stored Procedures in a SQL Database. I need to
give a Database Role in that Database Execute permissions for all of the
Stored Procedures I had created (but not the System Stored Procedures). Doin
g
this through the SQL Management Tool UI will take FOREVER. Is there another
way? If it's a script of some kind, can someone give me a hint or a sample?
This may be a bit beyond me.
AlexHi,
In SQL Server 2005 it is very easy to set the execute rights to all
procedures. See the script
USE DBNAME
GO
/* CREATE A NEW ROLE */
CREATE ROLE db_executor
/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
Assign this role to the database user and he will be able to execute all
procedures inside the databse
THanks
Hari
SQL Server MVP
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:1BCFF9ED-12DC-4FD3-921F-3A1E185DB18E@.microsoft.com...
>I have a massive number of Stored Procedures in a SQL Database. I need to
> give a Database Role in that Database Execute permissions for all of the
> Stored Procedures I had created (but not the System Stored Procedures).
> Doing
> this through the SQL Management Tool UI will take FOREVER. Is there
> another
> way? If it's a script of some kind, can someone give me a hint or a
> sample?
> This may be a bit beyond me.
> Alex|||Hi Alex,
Thanks for posting.
To execute a stored procedure, you have to be granted execute permission on
this stored procedure. As we know, stored procedure referenced as object in
SQL 2005. To grant permission on a object, we have a T-SQL command: Grant.
You can Grants permissions on a table, view, table-valued function, stored
procedure, extended stored procedure, scalar function, aggregate function,
service queue, or synonym.
For more information about Grant command, please refer to following link:
http://msdn2.microsoft.com/en-us/library/ms188371.aspx
Hope this information helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
--[vbcol=seagreen]
Doing[vbcol=seagreen]
another[vbcol=seagreen]
sample?[vbcol=seagreen]|||Hari -
Hi. This is very helpful. Can I ask one more detail? Assuming that I've
already created the database role, if I do "GRANT EXECUTE TO MyNewRole", wil
l
this grant execute permissions to ALL of the stored procedures in the
database (including system stored procedures)? I don't really want to do
that. Is there a way that I can have it grant execute only to stored procs
that I created (dbo.<procname> )? Or can I do it by matching a pattern in the
SP name? All of my stored procedures start with a special 3 character prefix
.
Can I use a "where" clause or something? And if I can, how?
Let me know?
Thanks!
Alex
"Hari Prasad" wrote:

> Hi,
> In SQL Server 2005 it is very easy to set the execute rights to all
> procedures. See the script
> USE DBNAME
> GO
> /* CREATE A NEW ROLE */
> CREATE ROLE db_executor
> /* GRANT EXECUTE TO THE ROLE */
> GRANT EXECUTE TO db_executor
> Assign this role to the database user and he will be able to execute all
> procedures inside the databse
> THanks
> Hari
> SQL Server MVP
>
> "Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
> news:1BCFF9ED-12DC-4FD3-921F-3A1E185DB18E@.microsoft.com...
>
>|||Hi,
As far as I know you need to give EXEC privilages to indvidual stored
procedures.
sample script:-
Run this in the database, then copy the results to the query window
and execute.
select 'Grant EXEC on ' + name + ' to WhomEver'
from sysobjects
where type = 'P'
Thanks
Hari
SQL Server MVP
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:E57C2633-AE66-4837-8769-A33CB3C36CE1@.microsoft.com...[vbcol=seagreen]
> Hari -
> Hi. This is very helpful. Can I ask one more detail? Assuming that I've
> already created the database role, if I do "GRANT EXECUTE TO MyNewRole",
> will
> this grant execute permissions to ALL of the stored procedures in the
> database (including system stored procedures)? I don't really want to do
> that. Is there a way that I can have it grant execute only to stored procs
> that I created (dbo.<procname> )? Or can I do it by matching a pattern in
> the
> SP name? All of my stored procedures start with a special 3 character
> prefix.
> Can I use a "where" clause or something? And if I can, how?
> Let me know?
> Thanks!
> Alex
>
> "Hari Prasad" wrote:
>|||> Is there a way that I can have it grant execute only to stored procs
> that I created (dbo.<procname> )? Or can I do it by matching a pattern in
> the
> SP name? All of my stored procedures start with a special 3 character
> prefix.
> Can I use a "where" clause or something? And if I can, how?
One method is to generate a script to do this. The example below was
developed for SQL 2000 but works with SQL 2005 too.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO MyNewRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1 AND
ROUTINE_SCHEMA = N'dbo' AND
ROUTINE_NAME LIKE N'usp%'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:E57C2633-AE66-4837-8769-A33CB3C36CE1@.microsoft.com...[vbcol=seagreen]
> Hari -
> Hi. This is very helpful. Can I ask one more detail? Assuming that I've
> already created the database role, if I do "GRANT EXECUTE TO MyNewRole",
> will
> this grant execute permissions to ALL of the stored procedures in the
> database (including system stored procedures)? I don't really want to do
> that. Is there a way that I can have it grant execute only to stored procs
> that I created (dbo.<procname> )? Or can I do it by matching a pattern in
> the
> SP name? All of my stored procedures start with a special 3 character
> prefix.
> Can I use a "where" clause or something? And if I can, how?
> Let me know?
> Thanks!
> Alex
>
> "Hari Prasad" wrote:
>

Changing login permissions

I am trying to set up a secure system and would like some advice on how to do it as it is a bit tricky.

The underlying security is Windows Authentication but I need to set various permission levels in the application.
What I want to do is to allow users read-only access to a users table. Once they are validated and their permission level is determined, then I want them to be assigned to the role that is set for their permission level.

I have looked at Application Roles but when I try to set the role up using the sp_setapprole then I get network errors (odd). There does not seem to be an SP which assigns a user to a role.

So I do I go about this?

Many thanks for your help.
Ian Logan

You can add a user to a role using sp_addrolemember.
The errors that you get for sp_setapprole are strange. Do you also get them when you're connected locally? Can you post these errors?

Thanks
Laurentiu|||

Can you please clarify how users are validated in your application? Is it handled by your aplication or SQLServer? If this is the latter, then you can assign permissions in advance to windows groups and your windows users who are the members of those groups will get those permissions upon logon to SQLserver. If this is former (i.e. you have applicatoin users, rather than windows or SQLServer principals) then you can
1) provide set of wrappers (such as table-valued functions) to access your data, which take application user name as an argument and have security logic inside; or
2) you can create a set of SQLServer users with necessary permission granted to them, map your application users to them using "EXECUTE AS user" feature on the TSQL procedure level or inside your application.

You can use sp_setapprole for 2, but I" believe it can be fully replaced by "EXECUTE AS"
sp_addrolemember is also available to add users to db roles, but itis not recommended to call it during your application on permanent basis, both due to security and reformance reasons.

|||

I am passing the Role Name and the Password to sp_setapprole. The role does exist in the database and it has permissions set for the various objects that I want to use.

I seem to be able to run sp_setapprole from the application, but as soon as I try to access the database again then the following error arises:

"System.Data.SqlClient.SqlException: General Network Error. Check your network documentation"

Note that I am using the Microsoft.Practices.EnterpriseLibrary.Data application block for all database interfacing. The application is obviously .NET and it is SQL 2000.

Note also that the code I am using works fine if I do not use sp_setapprole, i.e. the user has Windows Authentication and Public rights. (I have yet to REVOKE ALL FROM PUBLIC).

Many Thanks
Ian

Code:

Public Shared Function SetDBRole() As String

' Create the Database object, using the default database service. The

' default database service is determined through configuration.

Dim db As Database = DatabaseFactory.CreateDatabase()

Dim sqlCommand As String = "sp_setapprole"

Dim dbCommandWrapper As DBCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand)

' Add paramters

' Input parameters can specify the input value

dbCommandWrapper.AddInParameter("@.RoleName", DbType.String, "MyAppRole")

dbCommandWrapper.AddInParameter("@.Password", DbType.String, "MyPassword")

db.ExecuteNonQuery(dbCommandWrapper)

End Function

|||Ruslan

The users are to be validated by the application. However Windows Authentication is being used to allow them access to SQL Server in the first place. The users must logon separately to the application using a different user name and password from their Windows logon.

The application requires three tiers of user access (data entry, supervisor, etc) and the users of the application, plus their access level, are set up within the application.

The plan would be that then a new user is created then sp_addrolemember would be used to add then to the role. Then when they log in the users table would be looked up to determine their access level (role) and then sp_setapprole would be run to set their permissions.
Initially users would only have read-only access to the users table and no access to anything else. Actually, to be pedantic, nobody will have access to tables as I am using stored procedures for data access.

As for EXECUTE AS, I am using SQL 2000 and have not come across this. Is is 2005?

Kind Regards
Ian|||Ian, I don't understand why you both add users to a role and also calling sp_setapprole. I expect you could just use roles, have permissions assigned to them as appropriate, and then just have the users added to the appropriate role. The users would not be granted any permission directly, they would get their permissions from the role that they belong to. Wouldn't this address your security requirements?

Thanks
Laurentiu

PS: Yes, EXECUTE AS is a SQL 2005 feature.|||Laurentiu

Users would be added to a role when they were created within the application. However when they log in to the application then how is their role activated? I thought that you had to use sp_setapprole to do this.

As you may note from other parts to this thread, I am having problems using sp_setapprole. When I run it from SQL Query Analyser ( sp_setapprole "MyRole", "mypassword" ) then I get Msg 2762 saying that it has been invoked incorrectly. When I run it from .NET then I get the odd network error message as described in this thread.

I reckon that if I can get sp_setapprole to work then I should be there...

Kind Regards
Ian

|||

I've tried sp_setapprole on SQL 2000, and I would get error 2762 whenever I would try to set the approle again after it was already set. You can verify whether the approle is already set by executing:

select user_name()

This will return the approle name if it is already set.

If a SQL user is a member of a SQL database role, the role membership takes effect when the user connects to the database.

I still don't understand how authentication and authorization is handled by your app. You mentioned that:

"The users are to be validated by the application. However Windows Authentication is being used to allow them access to SQL Server in the first place. The users must logon separately to the application using a different user name and password from their Windows logon."

Does the app connect to SQL Server using the user's Windows credentials or by using some other Windows credentials? How are the users that you are adding to roles related to the users that connect to the app? Could you explain the steps that are involved when a person that uses the app logs in to it?

Thanks
Laurentiu

|||Laurentiu

I have discovered an article which explains some of the problems with sp_setapprole. It is 229564, and basically the connection pooling has to be disabled for sp_setapprole. Also, another post indicated that running sp_setapprole in SQL Query does not work, I think it was again due to connection issues. This looks like there may be a problem because connection pooling is useful and switching it off is a backward step.

This instance of SQL Server has only Windows Authentication.

1. The app connects using Windows Authentication.
2. On clicking OK on the app's Login, a user will be validated against a Users table in the database. This table will also contain their access level (1-3).
3. They will then be allocated one of three roles, based on their access level.

The user name and password in this table will be quite independent of their Windows user name and password as we want to make this as secure as possible. (We also need to encrypt at least one table as well - but that is another story).

The app will control the setting up of the users for the Users table. Only a superuser of the app will be able to do this. They will enter the user name, password and access level, and these will be stored in the Users table.

Kind regards
Ian
|||

I think I begin to understand. When you say roles, you really mean application roles, right, not database roles? You allocate a user to a role by calling sp_setapprole. I thought you were using both database roles and application roles.

I'll try to see if I can find anything else related to the limitations of using sp_setapprole with connection pooling.

Thanks
Laurentiu

|||Laurentiu

Any further thoughts on the connection pooling issue?

Kind Regards
Ian|||I inquired and there is no workaround on SQL Server 2000 to make sp_setapprole work with connection pooling.

Thanks
Laurentiu|||OK, many thanks. I will raise the connection pooling issue in another forum as I have some further questions about it.

Kind Regards
Ian Logan