I am looking for information regarding permissions needed to be able to run
1) the Query Analyzer and 2) Enterprise Manager.
Since we are required to revoke all access from the public role, all
inherited permissions needed to run the above applications are gone. I
can't seem to find any documentation on which objects are needed in order to
make those functional for a particular role.
Thanks in advance,
AllenHi,
To open a database in Enterprise manager or Query Analyzer you should be
user in that partcular database. Then to access the object
you should have minimum select rights on table and exec rights on procedures
Thanks
Hari
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:uCpzs$v5GHA.4304@.TK2MSFTNGP03.phx.gbl...
>I am looking for information regarding permissions needed to be able to run
>1) the Query Analyzer and 2) Enterprise Manager.
> Since we are required to revoke all access from the public role, all
> inherited permissions needed to run the above applications are gone. I
> can't seem to find any documentation on which objects are needed in order
> to make those functional for a particular role.
> Thanks in advance,
> Allen
>|||Thanks for your reply.
I understand you have to be a user in that database, but since I revoke all
access from public, simply adding a user to a database has a net result of
nothing. I need to know /specifically/ which objects the user would need
explicit access to.
Examples include:
- system tables in master
- sprocs and extended sprocs in master
- views in master (syslogins? sysconstraints?)
- system tables in user databases they need access to
etc...
I can take care of all the user objects - I need to know about system
objects. I can't just assign people to the db_owner database role either -
I need to grant them just enough privileges to use those two applications,
but no more.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uh8vHs15GHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hi,
> To open a database in Enterprise manager or Query Analyzer you should be
> user in that partcular database. Then to access the object
> you should have minimum select rights on table and exec rights on
> procedures
> Thanks
> Hari
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:uCpzs$v5GHA.4304@.TK2MSFTNGP03.phx.gbl...
>
Showing posts with label permissions. Show all posts
Showing posts with label permissions. Show all posts
Friday, February 17, 2012
Effective Permissions Error with Domain User
If I create a new Login at the server level for a domain group :i.e.,
MyDomain\Domain Users
Then I go add a user tied into this new Login to my database i.e., Northwind
or pubs to add a user tied to this login.
Why is it then when I go to Database Properties -> Permissions and select
Effective Permissions on this new user I get the following error:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot execute as the server principal because the principal "EGUIRES\Domain
Users" does not exist, this type of principal cannot be impersonated, or you
do not have permission. (Microsoft SQL Server, Error: 15406)
For help, click:
tSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
BUTTONS:
OK
The guest account seems fine for this but any manual entries I try that is
linked to my domain gets this error.
I would appreciate any light you can shed on this
Scott EguiresScott
Did you get these database from SQL Server 2000 by using a RESTORE command?
What is the compatibilty level of both databases?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
> If I create a new Login at the server level for a domain group :i.e.,
> MyDomain\Domain Users
> Then I go add a user tied into this new Login to my database i.e.,
> Northwind
> or pubs to add a user tied to this login.
>
> Why is it then when I go to Database Properties -> Permissions and select
> Effective Permissions on this new user I get the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Cannot execute as the server principal because the principal
> "EGUIRES\Domain
> Users" does not exist, this type of principal cannot be impersonated, or
> you
> do not have permission. (Microsoft SQL Server, Error: 15406)
> For help, click:
> EvtSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
> --
> BUTTONS:
> OK
>
> The guest account seems fine for this but any manual entries I try that is
> linked to my domain gets this error.
>
>
> I would appreciate any light you can shed on this
>
> Scott Eguires
>|||Yes I did. I set the database compatibility to 2005. WHat is interesting i
s
that Effective Permissions work for the Domain\Administrator group. I did a
server profile trace and found that it was calling the Execute As User. Thi
s
led me to the following clues:
When I run the following:
EXECUTE AS USER = 'MYDOMAIN\Domain Users';
I get the error:
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal
"MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
impersonated, or you do not have permission.
But if I run:
EXECUTE AS USER = 'MYDOMAIN\Administrator';
It works fine.
This leads me to believe it is some sort of permissions issue.
Any info would be greatly appreciated.
Thanks
"Uri Dimant" wrote:
> Scott
> Did you get these database from SQL Server 2000 by using a RESTORE command
?
> What is the compatibilty level of both databases?
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
>
>|||Scott
Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
the user a member of loca Administrator group on a computer when you run
SQL Server?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...[vbcol=seagreen]
> Yes I did. I set the database compatibility to 2005. WHat is interesting
> is
> that Effective Permissions work for the Domain\Administrator group. I did
> a
> server profile trace and found that it was calling the Execute As User.
> This
> led me to the following clues:
> When I run the following:
> EXECUTE AS USER = 'MYDOMAIN\Domain Users';
> I get the error:
> Msg 15517, Level 16, State 1, Line 1
> Cannot execute as the database principal because the principal
> "MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
> impersonated, or you do not have permission.
>
> But if I run:
> EXECUTE AS USER = 'MYDOMAIN\Administrator';
> It works fine.
> This leads me to believe it is some sort of permissions issue.
> Any info would be greatly appreciated.
> Thanks
>
> "Uri Dimant" wrote:
>|||No the 'MYDOMAIN\Domain Users' login is neither a member of the local
administrator
group nor a member of the sysadmin server role on the machine I am running
SQL Server.
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>|||Scott
I tried tp reproduce the problem on my worstation SQL Server 2005 (Dev
Edition)
1) Restore a database from SQL Server 2000 which had an user called 'John'
(I changed compatibility level to 90)
2) Create a new Login 'NewJohn' and granted him an access to the database
(non sysadmin)
3) Right Click on the database --Properties --Permissions ( I sow
'John' user along with'NewJohn' )
4) Click on Effective Permissions ( I got the same error)
5) Click on Remove button and removed 'John' user
6) Click on Effective Permissions again and now it working
I think it is because that user "John" was transfered as a database user
(orphaned) without mapping to any login on the new server
MS has provided two stored procedures to move the logins with their original
SID but it worked fime on SS2K and I have not tried yet on SS2005
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:4F36A7F9-3108-4142-875A-2DB8CA46ABB3@.microsoft.com...[vbcol=seagreen]
> No the 'MYDOMAIN\Domain Users' login is neither a member of the local
> administrator
> group nor a member of the sysadmin server role on the machine I am running
> SQL Server.
>
> "Uri Dimant" wrote:
>|||It seems that you cannot execute as user on a Group. Which makes sense sinc
e
the text is
Execute as User. So this tells me I will never be able to get the effective
permissions for a group
through the SQL Workbench tool which is fine I suppose. I had an additional
problem in that I had
my service account was set to local and needed to set it to a domain user
with appropriate rights. Without that my Execute as user fails since it
cannot resolve the user in my domain.
Scott Eguires
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>
MyDomain\Domain Users
Then I go add a user tied into this new Login to my database i.e., Northwind
or pubs to add a user tied to this login.
Why is it then when I go to Database Properties -> Permissions and select
Effective Permissions on this new user I get the following error:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot execute as the server principal because the principal "EGUIRES\Domain
Users" does not exist, this type of principal cannot be impersonated, or you
do not have permission. (Microsoft SQL Server, Error: 15406)
For help, click:
tSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
BUTTONS:
OK
The guest account seems fine for this but any manual entries I try that is
linked to my domain gets this error.
I would appreciate any light you can shed on this
Scott EguiresScott
Did you get these database from SQL Server 2000 by using a RESTORE command?
What is the compatibilty level of both databases?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
> If I create a new Login at the server level for a domain group :i.e.,
> MyDomain\Domain Users
> Then I go add a user tied into this new Login to my database i.e.,
> Northwind
> or pubs to add a user tied to this login.
>
> Why is it then when I go to Database Properties -> Permissions and select
> Effective Permissions on this new user I get the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Cannot execute as the server principal because the principal
> "EGUIRES\Domain
> Users" does not exist, this type of principal cannot be impersonated, or
> you
> do not have permission. (Microsoft SQL Server, Error: 15406)
> For help, click:
> EvtSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
> --
> BUTTONS:
> OK
>
> The guest account seems fine for this but any manual entries I try that is
> linked to my domain gets this error.
>
>
> I would appreciate any light you can shed on this
>
> Scott Eguires
>|||Yes I did. I set the database compatibility to 2005. WHat is interesting i
s
that Effective Permissions work for the Domain\Administrator group. I did a
server profile trace and found that it was calling the Execute As User. Thi
s
led me to the following clues:
When I run the following:
EXECUTE AS USER = 'MYDOMAIN\Domain Users';
I get the error:
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal
"MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
impersonated, or you do not have permission.
But if I run:
EXECUTE AS USER = 'MYDOMAIN\Administrator';
It works fine.
This leads me to believe it is some sort of permissions issue.
Any info would be greatly appreciated.
Thanks
"Uri Dimant" wrote:
> Scott
> Did you get these database from SQL Server 2000 by using a RESTORE command
?
> What is the compatibilty level of both databases?
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
>
>|||Scott
Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
the user a member of loca Administrator group on a computer when you run
SQL Server?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...[vbcol=seagreen]
> Yes I did. I set the database compatibility to 2005. WHat is interesting
> is
> that Effective Permissions work for the Domain\Administrator group. I did
> a
> server profile trace and found that it was calling the Execute As User.
> This
> led me to the following clues:
> When I run the following:
> EXECUTE AS USER = 'MYDOMAIN\Domain Users';
> I get the error:
> Msg 15517, Level 16, State 1, Line 1
> Cannot execute as the database principal because the principal
> "MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
> impersonated, or you do not have permission.
>
> But if I run:
> EXECUTE AS USER = 'MYDOMAIN\Administrator';
> It works fine.
> This leads me to believe it is some sort of permissions issue.
> Any info would be greatly appreciated.
> Thanks
>
> "Uri Dimant" wrote:
>|||No the 'MYDOMAIN\Domain Users' login is neither a member of the local
administrator
group nor a member of the sysadmin server role on the machine I am running
SQL Server.
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>|||Scott
I tried tp reproduce the problem on my worstation SQL Server 2005 (Dev
Edition)
1) Restore a database from SQL Server 2000 which had an user called 'John'
(I changed compatibility level to 90)
2) Create a new Login 'NewJohn' and granted him an access to the database
(non sysadmin)
3) Right Click on the database --Properties --Permissions ( I sow
'John' user along with'NewJohn' )
4) Click on Effective Permissions ( I got the same error)
5) Click on Remove button and removed 'John' user
6) Click on Effective Permissions again and now it working
I think it is because that user "John" was transfered as a database user
(orphaned) without mapping to any login on the new server
MS has provided two stored procedures to move the logins with their original
SID but it worked fime on SS2K and I have not tried yet on SS2005
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:4F36A7F9-3108-4142-875A-2DB8CA46ABB3@.microsoft.com...[vbcol=seagreen]
> No the 'MYDOMAIN\Domain Users' login is neither a member of the local
> administrator
> group nor a member of the sysadmin server role on the machine I am running
> SQL Server.
>
> "Uri Dimant" wrote:
>|||It seems that you cannot execute as user on a Group. Which makes sense sinc
e
the text is
Execute as User. So this tells me I will never be able to get the effective
permissions for a group
through the SQL Workbench tool which is fine I suppose. I had an additional
problem in that I had
my service account was set to local and needed to set it to a domain user
with appropriate rights. Without that my Execute as user fails since it
cannot resolve the user in my domain.
Scott Eguires
"Uri Dimant" wrote:
> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)