Permissions required for sp_databases

This may be the wrong forum for this - if so, please direct me to the appropriate forum.

I've recently upgraded to MSSQL 2005 and I'm trying to execute the sp_databases stored procedure, but I'm having what looks like permissions problems. I can execute the stored procedure, but it doesn't return any results. As far as I can tell, the problem is that my userid can't see any rows in sys.master_files (I can select from the view, it just doesn't return any rows).

I've tried any number of things to get this to work, but the only thing that's been successful is to change my userid's database role membership to db_owner. In the long run, this isn't really a viable solution for me, since my userid should only really have read(select) access to the database in question.

I'd appreciate any pointers that you can give me - I figure I'm doing something stupid, but haven't been able to figure out what, yet.

Thanks,
Jeff

If you grant VIEW ANY DEFINITION, you will be able to see this information for the databases that you can access. However, VIEW ANY DEFINITION will open other catalogs as well.

I suggest to write a wrapper procedure over sp_databases that is signed with a certificate and grant that certificate the VIEW ANY DEFINITION permission, then grant EXECUTE permission on the procedure to the principal that needs this functionality.

Thanks
Laurentiu

|||That seems to work, thanks!

Is there any way to grant permission for an individual database? My user is really only interested in the sp_databases row for a single database. The others are just ignored, so they could be dropped out of the result set with no loss of functionality (and that might limit the permissions that need to be granted).

Jeff
|||

You can use a signed procedure to filter the results and only display the row for a certain database. You cannot do this only using permissions.

Thanks
Laurentiu

|||So if a procedure is created as dbo a user who has readonly access to the db in which it was created wouldn't be able to query sysobjects or use sp_helptext to view procedure information? In 2000 the user would be able to see the procedure but this is not so in 2005.

Permissions required for sp_databases

This may be the wrong forum for this - if so, please direct me to the appropriate forum.

I've

recently upgraded to MSSQL 2005 and I'm trying to execute the

sp_databases stored procedure, but I'm having what looks like

permissions problems. I can execute the stored procedure, but it

doesn't return any results. As far as I can tell, the problem is that

my userid can't see any rows in sys.master_files (I can select from the view, it just doesn't return any rows).

I've tried any

number of things to get this to work, but the only thing that's been

successful is to change my userid's database role membership to

db_owner. In the long run, this isn't really a viable solution for me,

since my userid should only really have read(select) access to the

database in question.

I'd appreciate any pointers that you can

give me - I figure I'm doing something stupid, but haven't been able to

figure out what, yet.

Thanks,
Jeff

If you grant VIEW ANY DEFINITION, you will be able to see this information for the databases that you can access. However, VIEW ANY DEFINITION will open other catalogs as well.

I suggest to write a wrapper procedure over sp_databases that is signed with a certificate and grant that certificate the VIEW ANY DEFINITION permission, then grant EXECUTE permission on the procedure to the principal that needs this functionality.

Thanks
Laurentiu

|||That seems to work, thanks!

Is there any way to grant permission for an individual database? My user is really only interested in the sp_databases row for a single database. The others are just ignored, so they could be dropped out of the result set with no loss of functionality (and that might limit the permissions that need to be granted).

Jeff|||

You can use a signed procedure to filter the results and only display the row for a certain database. You cannot do this only using permissions.

Thanks
Laurentiu

|||So if a procedure is created as dbo a user who has readonly access to the db in which it was created wouldn't be able to query sysobjects or use sp_helptext to view procedure information? In 2000 the user would be able to see the procedure but this is not so in 2005.

Permissions in SQLExpress

Hello. I have a problem with assigning permissions in SQLExpress.
First a little background. A couple of weeks ago we upgraded from MSDE to
SQLExpress. The database came over fine, tables, stored procedures, roles
and since everything ran ok I am sure that it preserved all the permissions
from the roles. The database has two programs and two distinct groups of
users that create and edit data in the database. Program 1 is a complete
program, that is, it has been running for several months using the MSDE and
the program never skipped a beat when we switched to SQLExpress. Program 2
is in development and would be considered a beta version.
This is where I am having problems. For program 2, I have created more
stored procedures in the database. The users of both programs are in a role
called PWDNAdmin (actually they are all in the same group in the main
network). I have used an transact-sql statement to grant permission to
execute these procedures to the group (GRANT EXECUTE "mynewquery" to
'PWDNAdmin') within the SQL Server Management Express program. When I run
the program from a users computer it says that the query doesn't exist or
the user doesn't have permissions! So, to test to be sure that the query
is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
works. Now the question is, how do I grant execute permission without
granting db_owner status?
Hope that is clear.
Thanks,
Gerry Dyck

It looks like you connect to the database as a member of PWNDAdmin and try to grant the role permissions you don't have. The error you were getting is caused by running as a low privileged principal with no access to the procedures. You don't need to grant db_owner status to grant execute permission, you just need to be able to grant permissions on the object and one way to achieve this would be to connect to the database as the dbo before granting the permissions.

Thanks
Laurentiu

|||Actually, I have my own user in the database for my login and am assigned db_owner. I ran a query that showed that I indeed had db_owner status. Would it perhaps help if I wasn't in the PWDNAdmin group on the network?
Thanks,
Gerry
|||

But didn't you get db_owner status because you're a PWDNAdmin member and you made that a db_owner? Or were you a db_owner before that?

As a db_owner, you should be able to grant any permission on objects in the database.

Can you post the T-SQL grant statement that fails as well as the text and number of the error that you are receiving?

Thanks
Laurentiu

|||I was a db_owner before giving that to PWDNAdmin. You have arrived at my delima!!! I should, as a db_owner grant permissions on objects in the database.
The T-SQL is:
GRANT EXECUTE ON mystoredprocedure TO PWDNAdmin
To get the error I will have to get back to the office and run the program. I will do that tomorrow.
Thanks for your help in deciphering my problem. Sure as tomorrow is the shortest day, my problem will be some simple thing, but until I find it....uuuuggghh.
Gerry
|||

Maybe the mystoredprocedure resides in a special schema and you should prefix its name with the schema name. The statement may fail because it cannot find the procedure within the schemas it searches by default.

Thanks
Laurentiu

|||The statement itself works. What doesn't work is the program when the procedure is called.
|||I am not sure about the schema that the query belongs to.
Maybe I need to review, for myself as well as for you to help me better. The database I am using was upgraded from MSDE so it brought along all the permissions for PWDNAdmin and the stored procedures. One of my programs that has been using the database for a few months works without any problems after the upgrade to SQLExpress.
Now I have created stored procedures in a separate database located on my notebook (so I can develope at my home office) that I have then created in the database in question.
Here is the T-SQL that I used to create the procedures (well only one 'cause there are lots)
STart:
USE [ProcWareDOTNET]
GO
/****** Object: StoredProcedure [dbo].[AcctInitialLogSelect] Script Date: 12/21/2005 10:52:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[AcctInitialLogSelect]
AS
SET NOCOUNT ON;
SELECT InvoiceNumber, PONumber, DateReceived, VendorName, Value, Initials, ErrorDescription FROM ATInitialLog
Grant Execute on dbo.AcctInitialLogSelect to PWDNAdmin
END
Note the Start and End are so you know where the sql ends. Anywho. I have now discovered the problem. If I remove the Grant Excecute line then my problem goes away!!! Now why would that be? Very interesting.
|||

The grant ends up being part of the procedure definition, so when you will call the procedure, you will actually perform a grant on it. You should always use BEGIN and END to delimit the body of your stored procedure. Also, simply adding a GO before the grant would make the script run as intended, but I recommend always using BEGIN and END.

Thanks
Laurentiu

|||Thank you Laurentiu for your help is solving my problem. I am not trained in SQL and in fact all my programming knowledge is self taught. There are times when the scope of work requires venturing into new areas previously unknown. This is one of those cases. I have worked for years using Access databases and in the last year the scope of our work finally made us realize that it was time to lay Access to rest and get with the program and true SQL databases. As such, there are times when learning while doing means getting it done sooner and perhaps bulkier rather than getting it done with precision that only years of knowledge can bring.
Thanks again have great holiday season and terrific new year,
Gerry
|||

I am glad I could be of help. Happy holidays to you as well.

Thanks
Laurentiu

Permissions

Hello. I have a problem with assigning permissions in SQLExpress. First a
little background. A couple of weeks ago we upgraded from MSDE to
SQLExpress. The database came over fine, tables, stored procedures, roles
and since everything ran ok I am sure that it preserved all the permissions
from the roles. The database has two programs and two distinct groups of
users that create and edit data in the database. Program 1 is a complete
program, that is, it has been running for several months using the MSDE and
the program never skipped a beat when we switched to SQLExpress. Program 2
is in development and would be considered a beta version.
This is where I am having problems. For program 2, I have created more
stored procedures in the database. The users of both programs are in a role
called PWDNAdmin (actually they are all in the same group in the main
network). I have used an transact-sql statement to grant permission to
execute these procedures to the group (GRANT EXECUTE "mynewquery" to
'PWDNAdmin') within the SQL Server Management Express program. When I run
the program from a users computer it says that the query doesn't exist or
the user doesn't have permissions! So, to test to be sure that the query
is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
works. Now the question is, how do I grant execute permission without
granting db_owner status?
Hope that is clear.
Thanks,
Gerry DyckHi,
add that users to db_datareader or db_ddladmin(it will grant permission to
Data Defination Languages)
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"News" wrote:

> Hello. I have a problem with assigning permissions in SQLExpress. First
a
> little background. A couple of weeks ago we upgraded from MSDE to
> SQLExpress. The database came over fine, tables, stored procedures, roles
> and since everything ran ok I am sure that it preserved all the permission
s
> from the roles. The database has two programs and two distinct groups of
> users that create and edit data in the database. Program 1 is a complete
> program, that is, it has been running for several months using the MSDE an
d
> the program never skipped a beat when we switched to SQLExpress. Program
2
> is in development and would be considered a beta version.
> This is where I am having problems. For program 2, I have created more
> stored procedures in the database. The users of both programs are in a ro
le
> called PWDNAdmin (actually they are all in the same group in the main
> network). I have used an transact-sql statement to grant permission to
> execute these procedures to the group (GRANT EXECUTE "mynewquery" to
> 'PWDNAdmin') within the SQL Server Management Express program. When I run
> the program from a users computer it says that the query doesn't exist or
> the user doesn't have permissions! So, to test to be sure that the query
> is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
> works. Now the question is, how do I grant execute permission without
> granting db_owner status?
> Hope that is clear.
> Thanks,
> Gerry Dyck
>
>|||Hi,
add them to db_ddladmin or grant db_datareader
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"News" wrote:

> Hello. I have a problem with assigning permissions in SQLExpress. First
a
> little background. A couple of weeks ago we upgraded from MSDE to
> SQLExpress. The database came over fine, tables, stored procedures, roles
> and since everything ran ok I am sure that it preserved all the permission
s
> from the roles. The database has two programs and two distinct groups of
> users that create and edit data in the database. Program 1 is a complete
> program, that is, it has been running for several months using the MSDE an
d
> the program never skipped a beat when we switched to SQLExpress. Program
2
> is in development and would be considered a beta version.
> This is where I am having problems. For program 2, I have created more
> stored procedures in the database. The users of both programs are in a ro
le
> called PWDNAdmin (actually they are all in the same group in the main
> network). I have used an transact-sql statement to grant permission to
> execute these procedures to the group (GRANT EXECUTE "mynewquery" to
> 'PWDNAdmin') within the SQL Server Management Express program. When I run
> the program from a users computer it says that the query doesn't exist or
> the user doesn't have permissions! So, to test to be sure that the query
> is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
> works. Now the question is, how do I grant execute permission without
> granting db_owner status?
> Hope that is clear.
> Thanks,
> Gerry Dyck
>
>