August 11, 2004 at 7:55 pm
Can someone help me understand how SQL views logins, db users, system roles, etc, etc? Specifically, I would like to create a single login that's a db owner and is automatically added to each database. Similarily, I would like to create another user that's in the 'public' role that can read/write all the objects and run sp's, but can't alter tables, etc, and is also auto-added. Am I making sense? It seems like a simple request, but I'm confused by the interface (enterprise manager).
Many thanks.
August 12, 2004 at 8:12 am
DB Owner:
1) Create login.
2) Database Access tab - Put a check beside the database in the top section and then put a check beside db_owner in the bottom section. You'll need to this for each user database (the system databases such as master, msdb, tempdb and model are usually avoided).
If you want to do this for each new database, you'll have to set this the model database. Generally this isn't recommended... rather run a script whenever you create a new database.
User to read/write/execute
Follow steps above except check db_datareader and db_datawriter instead of db_owner.
There is no role that automatically grants the ability to execute stored procedures. I've written an article about how to do this, by creating an executor role, but you'd need to run a script periodically in order to keep the permissions updated.
K. Brian Kelley
@kbriankelley
August 12, 2004 at 8:40 am
Below is an alternate procedure to grant permission to all procedures. You can create a role called Execute_storedprocedure in model database and whenever you create a new database, execute the procedure locally to the database or schedule it to run periodically. Brian has provided an excellent explaination of the rest of the questions.
/*
This procedure grants Execute permissions to all procedures on a database to the role desired.
The default role is Execute_storedprocedure
*/
CREATE procedure sp_grant_permissions_proc @role varchar(100)='Execute_StoredProcedure' as
declare curname cursor for select name,user_name(uid) from sysobjects where xtype = 'P'
declare @procname varchar(100)
declare @username varchar(100)
open curname
fetch next from curname into @procname, @username
while @@fetch_status = 0
begin
declare @sql varchar(300)
set @sql = 'grant execute on ' + @username + '.[' + @procname + '] to Execute_Storedprocedure'
exec(@sql)
fetch next from curname into @procname, @username
end
close curname
deallocate curname
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply