June 18, 2009 at 4:06 am
Hi All,
I have a situation in which an application login calls stored procedures etc, but after a release, the object often needs to be dropped and re-created. This means that the permissions will also be lost and needs to be re-applied.
My concern is that, this is a bit of a tricky situation as one has to repeat code all the time when trying to keep tabs of permission scripts etc, so for May release, I will have a permission script for the application, for June release another script for the permission etc, it bothers me that this will become imaginable, I know one can have a group on the database and assign permission to the group etc.
Will this be the best way to do this or using certificates as I have heard about this on SQL 2005.
Any thoughts or ideas here ?
June 18, 2009 at 4:24 am
Is this proc created using a "execute as ..." clause ??
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 18, 2009 at 5:30 am
Its not created using impersonation, its just created as standard create proc. One option is to include a grant permission on the create script for the creation of the object, my concern is that this will be a bit messy in having a central repository, especially if one wants to grant permissions from Production onto Dev.
Another option is to always script permission all the time from production.
June 18, 2009 at 5:37 am
We grant the exec to a db-role and add users in that db-role.
IMO you need to investigate why the proc needs to be re-created ?
If it already exists, just use an "alter proc" statement in stead of a create proc.
But the easiest way will be drop/create and grant to db-role.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 18, 2009 at 5:46 am
As you can image, in a Dev environment its better to drop/re-create to proper version it and also the timestamp on crdate as well on sysobjects is sometimes useful.
Alter would not update the date i.e there is no alter date column on the sys schema.
Also, if a role was created, and permission on a procedure/table granted to that role, if the object was alter dropped, does permission need to be re-applied to the role or it will inherit it, I know for normal users, permissions will be dropped, not so sure about roles.
Also, how about SQL certificates in 2005, will this have a role to play here ?
June 18, 2009 at 7:23 am
- I haven't seen a grant exec to a certificate. :unsure:
- if you drop a proc, all related grants also vanish !
What you might do is :
1) create a db user "without login"
2) add that user to the db-owner group (to be safe with all auth)
3) create a schema for all "read" sprocs (I know that is tricky)
4) grant usage of schema "drreadprocs" to the db_role_read_procs_executors
5) add your users to that db-group.
6) create/alter sprocs usp_xyz WITH EXECUTE AS the_db_user_without_login
Because you granted at schema level, users of the created db-role can actually execute all sprocs of that schema.
Further more, these users no longer need auth on the tabls/views,... itself, becaus that auth is obtained because the proc will actually be executed as the_db_user_without_login who has the needed auth.
So all you have to do is grant connect to the db and add to the db-role you designed.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 18, 2009 at 7:34 am
ALZDBA (6/18/2009)
- I haven't seen a grant exec to a certificate. :unsure:- if you drop a proc, all related grants also vanish !
What you might do is :
1) create a db user "without login"
2) add that user to the db-owner group (to be safe with all auth)
3) create a schema for all "read" sprocs (I know that is tricky)
4) grant usage of schema "drreadprocs" to the db_role_read_procs_executors
5) add your users to that db-group.
6) create/alter sprocs usp_xyz WITH EXECUTE AS the_db_user_without_login
Because you granted at schema level, users of the created db-role can actually execute all sprocs of that schema.
Further more, these users no longer need auth on the tabls/views,... itself, becaus that auth is obtained because the proc will actually be executed as the_db_user_without_login who has the needed auth.
So all you have to do is grant connect to the db and add to the db-role you designed.
Sounds like a good idea, problem is that this means I will have to change 200 or so Procedures and I dont think this will get the seal of approval especially as they know that instead of changing code, one can easily grant permission.
Granting individual permissions is not a headache for me, but I was just looking for a more efficient way of doing this, and also if the DB got moved onto Dev, to apply same permissions will be a piece of cake.
Also may i ask if its good practice to drop all logins on a db first before trying to apply permissions, as what you will often find is that once a DB is moved elsewhere onto DEv, a new master db is obviously created, but the user DB's still have the orphan users. In order to reapply permissions, one can create the user on master again and synch up the permissions, there is an Sp to auto fix this or drop all orphaned users in every DB and re-create + re-apply permissions.
Thanks.
June 18, 2009 at 8:00 am
Dean Jones (6/18/2009)
...Sounds like a good idea, problem is that this means I will have to change 200 or so Procedures and I dont think this will get the seal of approval especially as they know that instead of changing code, one can easily grant permission.
Granting individual permissions is not a headache for me, but I was just looking for a more efficient way of doing this, and also if the DB got moved onto Dev, to apply same permissions will be a piece of cake.
Also may i ask if its good practice to drop all logins on a db first before trying to apply permissions, as what you will often find is that once a DB is moved elsewhere onto DEv, a new master db is obviously created, but the user DB's still have the orphan users. In order to reapply permissions, one can create the user on master again and synch up the permissions, there is an Sp to auto fix this or drop all orphaned users in every DB and re-create + re-apply permissions.
Thanks.
I use my little script to re-align the users:
use YYY -- User-db
go
print 'print @@servername + '' / '' + db_name()'
print 'go'
go
declare @username varchar(128)
declare @Musername varchar(128)
declare @UserType Char(1)
declare @sql_stmt varchar(500)
declare @ExcludeWindowsAccounts Char(1)
set @ExcludeWindowsAccounts = 'N' -- veranderen indien je geen windows accounts wil behandelen !
--cursor returns with names of each username to be tied to its respective
DECLARE user_cursor CURSOR FOR
SELECT su.name as Name, msu.name as MasterName , su.type as UserType
FROM sys.database_principals su
left join sys.sql_logins msu
on upper(su.name) = upper(msu.name)
where su.type in ('S', 'U', 'G')
-- WHERE su.sid > 0x00
ORDER BY Name
--for each user:
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username, @Musername, @UserType
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username NOT IN ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA', 'list of names you want to avoid') -- enkel aanvullen indien je een ID niet wenst de synchroniseren
BEGIN
if @Musername is null
begin
if @UserType in ('U','G')
begin
if @ExcludeWindowsAccounts = 'N'
begin
print 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomein**\' + @username + ''')'
print ' begin '
print 'exec sp_grantlogin N''NtDomein**\' + @username + ''''
print 'exec sp_defaultdb N''NtDomein**\' + + @username + ''', N'''+ db_name() + ''''
print ' end'
set @sql_stmt = '--Windows account gehad'
end
else
begin
set @sql_stmt = '--'
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = ''' + @username + ''', @LoginName = NULL, @Password = -- provide password'
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' + @username + ''''
end
PRINT @sql_stmt
print 'go'
print '--*** statements not executed !!! ***'
--EXECUTE (@sql_stmt)
END
FETCH NEXT FROM user_cursor INTO @username, @Musername, @UserType
END --of table-cursor loop
--clean up
CLOSE user_cursor
DEALLOCATE user_cursor
Print '** the end User-synchronisatie **'
On our more recent db servers, we try to apply the structur I mentioned.
If the server is windows authenticated, we even create a prallel windows group, grant login and db-groupmembership to that windows group and have the authorisation story handed over to the operational windows AD managing group.
All we need to do after restore, is drop the windows group of production at the dev db and grant the windows group of dev (+ group membership).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply