July 26, 2010 at 12:28 am
Hi,
I've got an account that does not belong to any server roles, but public. It has db_datareader role membership on a database.
It also has connect permission on the database which is default once you get access to the database.
There are no direct permissions on the SP I am trying to run using this account, however, in test environment I can run the SP, but in production I can't. What am I missing?
Thanks.
July 27, 2010 at 6:47 am
Is this user a member of a group that has permission for a certain role?
-Roy
July 27, 2010 at 7:08 pm
The domain group membership is complicated, but there are no direct permissions on the SP and on database level I could not find any group, that has permissions to execute
I've run this on both servers:
USE MYDB;
EXECUTE AS USER = 'Mydomain\myaccount_prod'; --'Mydomain\myaccount_dev'
SELECT *
FROM fn_my_permissions('dbo.MySP', 'Object')
ORDER BY subentity_name, permission_name ;
SELECT *
FROM fn_my_permissions(NULL, 'Database')
ORDER BY subentity_name, permission_name ;
SELECT *
FROM fn_my_permissions('Mydomain\myaccount_prod', 'User') --'Mydomain\myaccount_dev'
ORDER BY subentity_name, permission_name ;
REVERT;
GO
I got the following in prod:
entity_name,subentity_name,permission_name
(0 row(s) affected)
entity_name,subentity_name,permission_name
database,,BACKUP DATABASE
database,,BACKUP LOG
database,,CHECKPOINT
database,,CONNECT
database,,SELECT
(5 row(s) affected)
entity_name,subentity_name,permission_name
Mydomain\myaccount_prod,,ALTER
Mydomain\myaccount_prod,,CONTROL
Mydomain\myaccount_prod,,IMPERSONATE
Mydomain\myaccount_prod,,VIEW DEFINITION
(4 row(s) affected)
And this in test:
entity_name,subentity_name,permission_name
(0 row(s) affected)
entity_name,subentity_name,permission_name
database,,CONNECT
database,,SELECT
(2 row(s) affected)
entity_name,subentity_name,permission_name
Mydomain\myaccount_dev,,ALTER
Mydomain\myaccount_dev,,CONTROL
Mydomain\myaccount_dev,,IMPERSONATE
Mydomain\myaccount_dev,,VIEW DEFINITION
(4 row(s) affected)
Sorry, I got it wrong in the first post. There are actually two accounts, one for prod and one for test, but they are supposed to have the same permissions in the corresponding environment.
July 27, 2010 at 7:25 pm
The only difference I could find, was that in test the dev account owns its own schema (Mydomain\myaccount_dev) and has it as a default, while in prod, the prod account has dbo as the default schema and does not own any schemas. The SP belongs to dbo schema, so I don't see how this could have any effect.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply