SQL Server Permissions Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Server Permissions Question

Author

Topic

ScottMitchell

6 Posts

Posted - 2004-09-08 : 15:33:26

The other day my wife showed me how you could have a Database Project
in VS.NET , where you could have all the sprocs and such. Very nice,
especially when the customer keeps changing the data model - easy to
search and replace on the sprocs.

Anywho, there are some permission questions I have. I used to do
everything through Enterprise Manager (EM) - create the sproc,
right-click on the sproc, go to Manage Permissions, and set
permissions.

Now that I am doing it via T-SQL commands through VS.NET , I need to
grant permissions using the GRANT EXECUTE ON ... syntax. Here's what
a SQL query in my VS.NET project might look like:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

/****** Object: Stored Procedure dbo.sp_CopyActivityTypeToProgram
Script Date: 9/5/2004 11:09:37 PM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[sp_CopyActivityTypeToProgram]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_CopyActivityTypeToProgram]
GO

CREATE PROCEDURE sp_CopyActivityTypeToProgram
(@TypeID int, @Type char(3) ) AS

... SQL queries ...

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/* Permissions stuff */
GRANT EXECUTE ON [dbo].[sp_GetActivityType] TO admins
GO
GRANT EXECUTE ON [dbo].[sp_GetActivityType] TO accounting
GO

Notice that I am trying to grant access to two SQL Server *roles* -
admins and accounting. After I run this, if I go to EM, refresh my
sprocs, and view the permissions for the sproc just added, it shows no
permissions for anyone. If I then drop to Query Analyzer and paste in
the lines:

GRANT EXECUTE ON [dbo].[sp_GetActivityType] TO admins
GO
GRANT EXECUTE ON [dbo].[sp_GetActivityType] TO accounting
GO

Refresh EM and view the sproc's permissions, the permissions are set.
How come it doesn't work through my T-SQL script that creates the
sproc?

Another question: what's the T-SQL to view permissions for a
particular sproc? Basically I want to see what users/roles have
execute permissions for a particular sproc.

Thanks.

--

Scott Mitchell
mitchell@4guysfromrolla.com
http://www.4GuysFromRolla.com/ScottMitchell

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 15:37:28

The script should work. Run the script, then open up EM, then check the permissions of the sprocs.

And you shouldn't prefix stored procedures sp_. That's one of the number one no nos. SQL Server actually first looks in the master database to find the stored procedure when it is prefixed with sp_. Any other prefix is fine just not sp_.

EDIT:
I wonder if VS likes the GOs. GO is a Query Analyzer thing to indicate the end of a batch and to execute it.

Tara

ScottMitchell

6 Posts

Posted - 2004-09-08 : 15:52:58

quote:
Originally posted by tduggan

The script should work. Run the script, then open up EM, then check the permissions of the sprocs.

And you shouldn't prefix stored procedures sp_. That's one of the number one no nos. SQL Server actually first looks in the master database to find the stored procedure when it is prefixed with sp_. Any other prefix is fine just not sp_.

EDIT:
I wonder if VS likes the GOs. GO is a Query Analyzer thing to indicate the end of a batch and to execute it.

Tara

ScottMitchell

6 Posts

Posted - 2004-09-08 : 15:54:50

Oh crap, it lost my comment... sigh...

quote:
Originally posted by tduggan

The script should work. Run the script, then open up EM, then check the permissions of the sprocs.

This is precisely what I did. It didn't work, though, I promise. :-/

quote:
And you shouldn't prefix stored procedures sp_. That's one of the number one no nos. SQL Server actually first looks in the master database to find the stored procedure when it is prefixed with sp_. Any other prefix is fine just not sp_.

Yeah, I know, but when I started working on this project there were ~75 sprocs with the sp_ prefix, called from an assorted collection of desktop and Web based apps. There isn't the interest/budget from the client to warrant going in and fixing this.

Is there a T-SQL command to view the permissions for a paritcular sproc? Maybe EM is just acting up on me?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 15:59:29

syspermissions contains the "permissions granted and denied to users, groups, and roles in the database".

I think the problem is with GO in Visual Studio.

quote:

GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.

Tara

ScottMitchell

6 Posts

Posted - 2004-09-08 : 16:35:28

quote:
Originally posted by tduggan

syspermissions contains the "permissions granted and denied to users, groups, and roles in the database".

I think the problem is with GO in Visual Studio.

quote:

GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.

Any idea for a workaround, then? I tried moving the GRANT statements to a separate SQL script in VS.NET, but still had no luck.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-08 : 16:48:06

Hmmm, it worked for me. I created a Database project. Then added a SQL script. The SQL script contained:

GRANT EXEC ON StoredProcedure1 TO User1

I then went to Project menu item, then Run. The permission was successfully added and I verified it in EM.

Does that not work for you?

Tara

ScottMitchell

6 Posts

Posted - 2004-09-08 : 17:15:28

I found the problem - me. Gadzooks, I am a moron. My GRANT statements were referring to a different sproc, so I was looking in the wrong place. Jebus.

Thanks for your patience, it is much appreciated. Now I am going to go beat myself with a clue bat.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-09-09 : 01:59:09

As an FYI...
Visual Studio handles GOs fine in database projects. I use them all the time.

Damian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-09 : 15:42:04

I was hoping you'd chime in about the GOs.

Tara

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-09-09 : 23:27:51

I do seem to spend a lot of time explaining them to people don't I