August 1, 2008 at 10:14 am
I wanted to take a query and throw it in a StoredProd. From there, I want a user to simple have access to JUST this proc to view the results. But, when I create this proc and grant execute on it to a user, they recieve privs denied on databases/sysusers/etc.
How do I grant the procedure rights so I don't have to grant it to the user...? Sorry if this is silly Q 🙁
CREATE PROCEDURE uSP_Accounts
-- Add the parameters for the stored procedure here
AS
set nocount on
go
Create table ##Temp12122121
(
ID_USER varchar(88),
SID varbinary(200),
NM_DATABASE varchar(66),
CD_ALTNAME varchar(44),
Domain_Acct varchar(22)
)
DECLARE @DB char(55)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT name
FROM master..sysdatabases
OPEN c1
FETCH NEXT FROM c1
INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
execute
(
'Insert ##Temp12122121 (ID_USER, CD_ALTNAME, SID, NM_DATABASE, Domain_Acct)
select
UPPER(b.name) ''ID_USER'',
case when (a.name is null) then ''*No Login On Server''
else UPPER(a.name) End ''CD_ALTNAME'', B.SID, ''' + @DB + ''' as NM_DATABASE , b.isntname as Domain_Acct
from master..syslogins a right outer join [' + @DB + ']..sysusers b on a.sid = b.sid
where(b.islogin = 1) and b.isaliased = 0
and b.name <> ''INFORMATION_SCHEMA''
and b.name <> ''SYSTEM_FUNCTION_SCHEMA'';'
) grant execute on usp_accounts to cbtest2
FETCH NEXT FROM c1
INTO @DB
END
CLOSE c1
DEALLOCATE c1
INSERT ##Temp12122121 (ID_USER, SID, NM_DATABASE, CD_ALTNAME, Domain_Acct)
select loginname as ID_USER, sid as SID, @@servername as NM_DATABASE, '--ServerLogin--' as CD_ALTNAME, isntname as Domain_Acct from master..syslogins;
select * from ##Temp12122121
ORDER BY ID_USER;
drop table ##Temp12122121;
August 1, 2008 at 10:38 am
Also, to Add. This is for SQL 2000.
August 1, 2008 at 11:39 am
Too long....I did not go through all, but, here is an error:
set nocount on
go
remove that "go"....
----------------
Edit:
Going through the code this time, what the use of ##Temp12122121? I think you could get rid of cursor.
August 1, 2008 at 11:41 am
Done.
I don't think it is possible w/o apply the end user to access to each db and select on the objects w/in the stored proc......
August 1, 2008 at 11:50 am
Chain of ownership gets broken when you use dynamic sql.
August 1, 2008 at 11:55 am
..how would that be since they are all system tables...
For stored procs, is it possible for the privileges to be help at the SP level.
Meaning, say I have DATABASE1 and Table ABC. I want Folks to select a field from there.
So, I'll create a proc that says select * from ABC. Do I have to give all the individuals SELECT on that table on that database.....? Or, would giving them execute on the new stored proc suffice.....?
August 1, 2008 at 12:16 pm
If Table1 and Sproc1 are owned by the same person, you only grant EXECUTE and it will work. The permissions transfer.
Once you cross databases, the ownership doesn't necessarily transfer. You can enable this, but I'm not sure how this works with master. My guess is the users might need rights in master to run this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 1, 2008 at 12:19 pm
The permissions on the stored procedure should suffice unless the table is in another database. (crossdatabasechaining option)
I've tried your code. The procedure is created by a db_owner in tempdb.
Errors I've got:
User ... is not know in database "model" (?)
Grant execute... Grantor does not have GRANT permission.
The go after the nocount gives troubles as mentioned above.
CREATE PROCEDURE dbo.uSP_Accounts
-- Add the parameters for the stored procedure here
AS
set nocount on
Create table ##Temp12122121
(
ID_USER varchar(88),
SID varbinary(200),
NM_DATABASE varchar(66),
CD_ALTNAME varchar(44),
Domain_Acct varchar(22)
)
DECLARE @DB char(55)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT name
FROM master..sysdatabases
OPEN c1
FETCH NEXT FROM c1
INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
execute
(
'Insert ##Temp12122121 (ID_USER, CD_ALTNAME, SID, NM_DATABASE, Domain_Acct)
select
UPPER(b.name) ''ID_USER'',
case when (a.name is null) then ''*No Login On Server''
else UPPER(a.name) End ''CD_ALTNAME'', B.SID, ''' + @DB + ''' as NM_DATABASE , b.isntname as Domain_Acct
from master..syslogins a right outer join [' + @DB + ']..sysusers b on a.sid = b.sid
where(b.islogin = 1) and b.isaliased = 0
and b.name <> ''INFORMATION_SCHEMA''
and b.name <> ''SYSTEM_FUNCTION_SCHEMA'';'
) --grant execute on usp_accounts to cbtest2
FETCH NEXT FROM c1
INTO @DB
END
CLOSE c1
DEALLOCATE c1
INSERT ##Temp12122121 (ID_USER, SID, NM_DATABASE, CD_ALTNAME, Domain_Acct)
select loginname as ID_USER, sid as SID
, @@servername as NM_DATABASE, '--ServerLogin--' as CD_ALTNAME
, isntname as Domain_Acct from master..syslogins;
select * from ##Temp12122121
ORDER BY ID_USER;
drop table ##Temp12122121;
August 1, 2008 at 12:27 pm
The grant err was a hiccup on my end when I was apply privs. you would have to remove...
--grant execute on usp_accounts to cbtest2
But, the privs are in other databases as it has a cursor thru each database on the server..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply