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.
| Author |
Topic |
|
PaulMoxon
Starting Member
5 Posts |
 Posted - 2009-07-09 : 11:18:51
|
| Hi All,I am having the following issue. I have setup a group in Active Directory, Added the users and added as a login on the SQL server. Currently there is only one user in the group (testing). The permissions they have are the same as what was their normal SQL login with the exception that DBO has been removed. When they try to run a process they get an authoirty issue on populating a linked database using a stored procedure (execute permission denied). I granted execute rights to the group and still the same issue.I gave the group DBO and still the same issue. Finally I was forced to add the user account back in (with DBO) to get this to work. We are trying to move away from individual logins and use AD groups, we also want to try and eliminate use of DBO for normal users. I am learnign SQL admin on the fly as it were so this is a bit vexxing as in theory this should be fine.thanks in advanceMoxy. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2009-07-09 : 14:40:12
|
| When you say "linked database" do you really mean a database on a Linked SQL Server? If so, you have to grant permissions on that server, too. They don't just automatically transfer over there.Another idea...does the stored procedure use Dynamic SQL? With Dynamic SQL you have to grant SELECT permissions on the underlying tables, not just EXECUTE to the stored procedure.--------------------------------------------Brand yourself at EmeraldCityDomains.com |
 |
|
|
PaulMoxon
Starting Member
5 Posts |
Posted - 2009-07-10 : 04:09:19
|
| Sorry, didnt phrase that very well. The database(s) are on the same server.when the user runs a process(job) using one of the databases it calls a stored procedure under another database on the same server. The failing error is when the stored procedure is called.I will try granting select permissions and execute to the tables as suggested.Thanks for the advice, I will report back later.Moxy.***** Update Sigh... turns out that within the database that the user was running the process from there was a stored procedure with the same/similar name to the one in another datanase that I thought was causing the problem, once I gave the user access to execute on this it worked fine.many thanks again. |
|
|
|
|
|
|
Using groups from Active Directory query on SQL