SQL Server 2005 User Permissions

[Walter_Ego]

I am develoing a system which cannot permit users performing adhoc queries,
how can I create a user in SQL 2005 with permissions to run stored procedures only?

Thx

[szlamany]

I am develoing a system which cannot permit users performing adhoc queries,
how can I create a user in SQL 2005 with permissions to run stored procedures only?

Thx

We do exactly that in our app's.

The USERS are granted EXEC permission to the SPROCS - that permits them only access to stored procedures, no table access without it being wrapped in a SPROC.

This also means that your UI app cannot perform adhoc queries - but everything done through a stored procedure.

We like it that way here.

[Walter_Ego]

Must I grant permission for each procedure?
(Theres quite a few in here)

[szlamany]

We script all our stored procedures in TEXT files - see post #5 in this thread:

http://www.vbforums.com/showthread.p...ight=fundsuser

With that done, then every script grant's it's own access - it's one of our SQL RULES TO LIVE BY.

It would also be very easy to loop through all the SPROCS and UDF's in the object table and grant permissions as well. I've got a sample of code for that at the office if you would consider writing a script to loop through those objects.

[Walter_Ego]

We also create all of our procs in text files, but dont do the access grants there, I do think it is a good idea and will consult it with the other developers.
(It'd also be nice to have a look at that loop script)

[Walter_Ego]

I already done it, is this OK?

DECLARE MY_CURSOR Cursor
FOR 
Select name from sysobjects where xtype = 'P'
Open My_Cursor 
DECLARE @ProcName VARCHAR(60),
            @Command VARCHAR(100)
Fetch NEXT FROM MY_Cursor INTO @ProcName
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @Command = 'GRANT EXECUTE ON ' + @ProcName + 'TO MyUser' 
EXEC(@Command)
FETCH NEXT FROM MY_CURSOR INTO @ProcName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

[Walter_Ego]

Last question:
Will this restrict the user from accessing the table directly thru adhoc queries or must I write a similar script denying access to all the table objects?

[szlamany]

There is no need to "disallow" table access - giving EXEC access to SPROCS "permits" those SPROCS to do whatever you want - but does not afford table access to users.

The only time we've done this type of looping was to "DELETE" all the UDF's and SPROC's (and VIEWS, INDEXES and CONSTRAINTS) in a database before giving it to a data warehouse outfit. They only needed the data and could take it as a SQL BACKUP (.BAK) file - so we looped through objects of certain types DROPPING them...

Here's the code:

BACKUP DATABASE Stufiles
   TO DISK = 'c:\stufiles_production_DO_NOT_SEND.bak'
RESTORE FILELISTONLY 
   FROM DISK = 'c:\Stufiles_production_DO_NOT_SEND.bak'
RESTORE DATABASE Stufiles_ES
   FROM DISK = 'c:\Stufiles_production_DO_NOT_SEND.bak'
   WITH MOVE 'Stufiles_data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\data\Stufiles_ES.mdf',
   MOVE 'Stufiles_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\data\Stufiles_ES.ldf'
GO
Use Stufiles_ES
Go
DROP TABLE APPCONNECT_T
DROP TABLE APPELEMROLE_T
DROP TABLE APPELEM_T
DROP TABLE USERROLE_T
DROP TABLE ROLE_T
DROP TABLE USER_T
DROP TABLE CLSATTCLOSE_T
DROP TABLE CLSPOSTED_T
DROP TABLE CONFLICT_T
DROP TABLE EMERG2_T
DROP TABLE GUARDIAN2_T
DROP TABLE GUARDIAN3_T
DROP TABLE INFORMATION_T
DROP TABLE REOPEN
DROP TABLE REPORTNAME_T
DROP TABLE REPORT_T
DROP TABLE SCHOOLPERIOD_T
DROP TABLE STUFILES_T
DROP TABLE TEACHEROLD_T
Go
DECLARE @PROCNAME VARCHAR(255)
DECLARE @PARENT VARCHAR(255)

DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='P' and LEFT(NAME,3)<>'dt_' ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
WHILE @@FETCH_STATUS=0
BEGIN
	PRINT @PROCNAME
	EXEC ('DROP PROCEDURE '+@PROCNAME)
	FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR

DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='FN' ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
WHILE @@FETCH_STATUS=0
BEGIN
	PRINT @PROCNAME
	EXEC ('DROP FUNCTION '+@PROCNAME)
	FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR

DECLARE DROP_CURSOR CURSOR FOR select name from sysobjects where type='V' AND LEFT(NAME,3)<> 'sys' ORDER BY NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
WHILE @@FETCH_STATUS=0
BEGIN
	PRINT @PROCNAME
	EXEC ('DROP VIEW '+@PROCNAME)
	FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR

DECLARE DROP_CURSOR CURSOR FOR select s1.name,s2.name from sysobjects s1 
				left join sysobjects s2 on s2.id=s1.parent_OBJ where s1.type='F' ORDER BY s1.NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME,@PARENT
WHILE @@FETCH_STATUS=0
BEGIN

	PRINT @PROCNAME+' '+@PARENT
	EXEC ('ALTER TABLE '+@PARENT+' DROP CONSTRAINT '+@PROCNAME)
	FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME,@PARENT
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR

DECLARE DROP_CURSOR CURSOR FOR select s1.name,s2.name from sysobjects s1 
				left join sysobjects s2 on s2.id=s1.parent_OBJ where s1.type='K' ORDER BY s1.NAME
OPEN DROP_CURSOR
FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME,@PARENT
WHILE @@FETCH_STATUS=0
BEGIN
	PRINT @PROCNAME+' '+@PARENT
	EXEC ('ALTER TABLE '+@PARENT+' DROP CONSTRAINT '+@PROCNAME)
	FETCH NEXT FROM DROP_CURSOR INTO @PROCNAME,@PARENT
END
CLOSE DROP_CURSOR
DEALLOCATE DROP_CURSOR
GO
BACKUP DATABASE Stufiles_ES
   TO DISK = 'c:\stufiles_ES.bak'

[szlamany]

Are you using a ROLE to grant access to - so that you only have to give that ROLE to the WINDOWS USER (or WINDOWS GROUP)???

[Walter_Ego]

We plan to use Application Roles, yes.
I can mold this script to use a Role instead of a User correct?

[szlamany]

We plan to use Application Roles, yes.
I can mold this script to use a Role instead of a User correct?

We usually have just one ROLE in our DB - like StufilesUser or FundUser or AcctfilesUser.

We do:

GRANT EXECUTE ON frmExceptions_Inquire TO FundsUser

for example - giving the FundsUser ROLE EXECUTE to a SPROC (frmExceptions_Inquire in the example).

Then it's up to the customer to chose to either put each user into SQL security and associate them with the ROLE, or to create WINDOWS GROUPS, associate the ROLE with the GROUP and then assign user to the GROUP.

[edit] - Although ours are DATABASE ROLES not APPLICATION ROLES - never used APPLICATION ROLES (they require password to turn on - right?)

[Walter_Ego]

Hi there again - update
Am testing this now, created a SQL Login called tstLogin
Created a user in this database called tstUser
tstLogin is mapped to tstUser

tstUser is in the tstRole group
tstRole group has execute permission on the GetRecords SPROC

When I login as tstLogin, and execute the GetRecords SPROC, I get the 'SELECT permission denied' error on the table being queried from the SPROC. What am I doing wrong?

[szlamany]

tstLogin is mapped to tstUser?

What does that mean?

[Walter_Ego]

I was asked for the Login Name when I created tstUser.
Now tstUser shows in the tstLogin properties under the 'User Mapping' tab.

[szlamany]

Ok - first of all I've never used SQL 2005 Management studio - so I'm just getting into it now on my laptop - we've got one SQL 2005 instance running on a server here at the office...

BTW - why are you using a SQL LOGIN and not a WINDOWS LOGIN? Do you really want to ask the user for a username and password when they get into the app?

ok - I went to the SECURITY branch - opened up LOGINS - right clicked and selected NEW LOGIN...

What did you do at that point?

[kaffenils]

The USERS are granted EXEC permission to the SPROCS - that permits them only access to stored procedures, no table access without it being wrapped in a SPROC.

Just keep in mind that if a SPROC accesses objects in other databases, then the user will need explicit access to those objects.

[szlamany]

Just keep in mind that if a SPROC accesses objects in other databases, then the user will need explicit access to those objects.

But that is specifically other databases - right??

[kaffenils]

But that is specifically other databases - right??

Yes.

[kaffenils]

But that is specifically other databases - right??

To clarify a little bit more:
If a view, function or sproc in DB_1 accesses an object (that be a table,view, sproc...) in DB_2, then the user in DB_1 will need appropriate access the this object in DB_2.

That means that if you have a sproc called SPROC_1 in DB_1 that reads data from a table named TABLE_2 in DB_2, the user that exeutes SPROC_1 will need permission to execute this sproc and SELECT from TABLE_2 in DB_2. If there is a sproc called SPROC_2 in DB_2 that selects from TABLE_2 in DB_2, and SPROC_1 in DB_1 executes SPROC_2 in DB_2, then the user will need execute permission on SPROC_1 in DB_1 and SPROC_2 in DB_2. The user no longer need SELECT access to TABLE_2 in DB_2.

[szlamany]

That makes complete sense - thanks for filling my greymatter further

[kaffenils]

That makes complete sense - thanks for filling my greymatter further

Well, ut confused me the first time I noticed that EXECUTE permission to the sproc wasn't enough. That only objects in the same database could be accessed from a sproc without explicit permissions to them.

But, as you say, it kinda' make sense that this is the way it is done.

[Walter_Ego]

BTW - why are you using a SQL LOGIN and not a WINDOWS LOGIN? Do you really want to ask the user for a username and password when they get into the app?
ok - I went to the SECURITY branch - opened up LOGINS - right clicked and selected NEW LOGIN...
What did you do at that point?

After that go to one of the databases and open up the security>Users Branch and create a new user. It asks you for a login. If you go to the properties of the login itself and click on the 'User Mappings' tab you will see this user.

We need to use SQL Server security logins because not all of the users will belong in the same domain.

Guys, its been solved using the EXECUTE AS OWNER clause:

CREATE PROCEDURE HRGetRecords 
@Dept int
WITH EXECUTE AS OWNER
AS
SELECT ... ... ...

Thanks for all the help!

[kaffenils]

Guys, its been solved using the EXECUTE AS OWNER clause

Nice. Thats a new SQL Server 2005 feature.

[szlamany]

btw - there is a link in my signature on how to create "non-domain" windows authenticaton access to servers...

You do not have to be in the domain to use WINDOWS AUTHENTICAION - if you have WINDOWS XP PRO as the operating system.

[szlamany]

Nice. Thats a new SQL Server 2005 feature.

I just looked in my MSDN library and I can see how EXECUTE AS changes the "login credentials" of the user - but that would seem to me that it fixed the symptom and not the problem...

I'm certainly not looking forward to adding EXECUTE AS statements to over a 1000 SPROCS to make SQL 2005 work

[kaffenils]

I just looked in my MSDN library and I can see how EXECUTE AS changes the "login credentials" of the user - but that would seem to me that it fixed the symptom and not the problem...

I'm certainly not looking forward to adding EXECUTE AS statements to over a 1000 SPROCS to make SQL 2005 work

From what I understand when reading BOL, you don't have to specify EXECUTE AS. If you omit EXECUTE AS it will run with the permissions of the current user (CALLER)

[szlamany]

From what I understand when reading BOL, you don't have to specify EXECUTE AS. If you omit EXECUTE AS it will run with the permissions of the current user (CALLER)

Which leads to me being confused about how it corrected the actual problem that Walter_ego is having. The attempt was to "GRANT EXEC" rights to a SPROC to a user - which would yield "table rights" within that SPROC.

[kaffenils]

Which leads to me being confused about how it corrected the actual problem that Walter_ego is having. The attempt was to "GRANT EXEC" rights to a SPROC to a user - which would yield "table rights" within that SPROC.

Now I'm confused too. I don't have a 2005 server to test on right now, but I will set one up just to fiund out how permissions are checked in stored procedures.

[Walter_Ego]

Yes, the user now has GRANT permission on the SPROCS but not on the underlying tables.
Using EXECUTE AS OWNER the user will have 'virtual' permission to access data on that table only when calling the SPROC, and will not be able to perform adhoc queries against the server. EXECUTE AS USER is the default behavior.