January 7, 2013 at 10:39 am
We have permissions set up so users have permissions only on stored procs. I have a role set up with Execute permission on DB and have added user to the role. The role has a deny on only 1 specific SP (which is NOT being used in this case). The SP will run for me (as sysadmin), (being run via .net pgm) but not for the user. Also, the other 3 SPs will run for the user fine. Any suggestions on how I can troubleshoot this problem from the database side? Thanks for the help!
January 7, 2013 at 10:59 am
Error messages for the ones not working?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2013 at 11:04 am
unfortunately not. the SP in question is supposed to add records to a table - and this isn't happening. Only when I run the app.
January 7, 2013 at 11:06 am
Is the table in the same database or a different database?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2013 at 11:14 am
The sp is querying a linked table (non-sql db) to get the records to add to the SQL Server table. The other 3 queries ARE working which have the same setup (different tables).
Also... the same login (a generic one we've been given) is used to access the non-sql db. So, same login used against that one irregardless of whoever is running the sp
January 7, 2013 at 11:22 am
Does the table involved in the insert, or any of the tables in the non-functioning sp, have deny permissions for the users or role?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2013 at 11:31 am
All users have connect permissions only
Role has execute permission - and user is part of role - this is how they are granted execute permissions -via this role
Role has deny execute on 1 sp only - which isn't used in this scenario
there are no permissions against tables directly
January 7, 2013 at 11:35 am
And the linked table, does this role have the appropriate permissions on it?
Have you tried to run the proc while logged in as a user in that role (not yourself) from within ssms?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 7, 2013 at 12:32 pm
The linked server has been set up to use a generic login rather than the login used by sql server. However, when I tried to run the sp as another user, (including myself!) I get the following error: (so not sure why that is happening - if I run it without use of the 'EXECUTE AS user =' line, it runs fine)
Msg 15274, Level 16, State 1, Line 2
Access to the remote server is denied because the current security context is not trusted.
January 7, 2013 at 1:42 pm
Have you tried running the linked server query from SSMS both as yourself and with the EXECUTE AS USER = ? It could be a permissions issue with the other DBMS that is sending back a return code that SQL Server is mis-interpreting.
January 7, 2013 at 2:04 pm
I ran it as the other user. I ran it as myself (both times using execute as user). Got the same error message. Running it WITHOUT execute as user, it runs fine for me.
In the meantime I just tried using 'execute as login'. I seem to be getting somewhere using that, as I am now getting:
'Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'Program', database 'mydbname', schema 'dbo'.
However, users have no permissions against any tables - only execute permissions via the role.
I get data from linked server using OPENQUERY. The returned data links to local SQL Server table & is then inserted into SQL Server table. (@sqlqry has been put together in stmts prior to this running):
SET @Mysql = N'
INSERT INTO MySQLServerdb.dbo.Mytable
(
columns
)
SELECT
listed columns
FROM OPENQUERY(linked_server_Name,' + @sqlqry + ') AS s
INNER JOIN Program AS p ON s.program_id = p.ProgramID
WHERE s.Col_ID NOT IN (SELECT ColID FROM MyTable)'
exec @Mysql
January 7, 2013 at 2:14 pm
I replied once to this - and it didn't 'take', so trying again, but shorter reply:
I tried using execute as login instead and am now getting:
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'Program', database 'MyDB', schema 'dbo'.
the Program table (sql server) inner joins with the results returned via the OPENQUERY against the linked server.
I can run the sp myself otherwise.
January 7, 2013 at 9:54 pm
If you stub a proc with the same permissions, do you get results from the openquery piece of the query? Eliminate the Join and the insert - make sure results are being passed back from the openquery linkedserver.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply