Friday, March 9, 2012

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
>
>

No comments:

Post a Comment