SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Copy Permissions for a database


Copy Permissions for a database

Author
Message
RyanRandall
RyanRandall
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2751 Visits: 4652
Comments posted to this topic are about the item Copy Permissions for a database

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
georgianne.giese
georgianne.giese
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 25
The function bo.f_lookup_system_id is not defined and the link http://www.sqlservercentral.com/columnists/awarren/sqlpermissionspublicrole_2.asp is no longer working.

Any suggestions on where I can find this function?Smile

--Georgianne
smorgan-607537
smorgan-607537
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 307
I got the same thing, missing f_lookup_system_id.

Can I get that code?
RyanRandall
RyanRandall
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2751 Visits: 4652
Gosh. This is a long time ago.

I'd forgotten all about this until I got an email from smorgan just now. And I can barely remember anything about it, so my starting point is not that much different from you guys.

I did quite a bit of digging but didn't find much which is directly useful.

I can't be sure, but I think this article might now be a link for the one referred to:
http://www.sqlservercentral.com/articles/Security/sqlpermissionspublicrole/116/

Given that, I think the f_lookup_system_id function probably looked something like this (this is a guess, but it should at least give the idea):

create function dbo.f_lookup_system_id(@name varchar(50), @id int) returns varchar(50) as
/*
select dbo.f_lookup_system_id('sysprotects_protecttype', 206)
select dbo.f_lookup_system_id('sysprotects_action', 197)
*/
begin
declare @s varchar(50)

if @name = 'sysprotects_protecttype'
select @s =
case @id
when 204 then 'GRANT_W_GRANT'
when 205 then 'GRANT'
when 206 then 'REVOKE'
end

if @name = 'sysprotects_action'
select @s =
case @id
when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE'
end

return @s
end



Sorry I can't be more helpful.

If you manage to get anything useful out of this, please let us know! Smile

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
smorgan-607537
smorgan-607537
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 307
Thanks! I may need to get back into that.

For now, I used some of your code in the previous article and modified it. I am trying to copy Roles (and some users) from one DB to another on the same server. Here's what I got to work for me (this is a SQL 2000 server):


---------------------------------------------------------------
-- COPY USERS & ROLES
-- from 1 db on this server to another db on this server
---------------------------------------------------------------
-----------------------
-- change all CA_AugME to "from" DataBase (OLD db)
-- change all CA_SeptME to "to" DataBase (NEW db)
--
-- If the user had no permissions to DB, this will copy that from the "from" db and
-- print a msg in grid 'ADDED USER ', @user_name
--
-- All Roles on the "from" db will be applied to the "to" db

-------------------------------------------------------
-- based on code from http://www.sqlservercentral.com/scripts/T-SQL+Aids/30754/
-------------------------------------------------------

-- Copy Roles
USE CA_SeptME

declare @s varchar(1000)
DECLARE c_from_user_roles CURSOR
READ_ONLY
FOR
select u.name as UserName, a_ROLE.name as RoleName --, *
from CA_AugME.dbo.sysusers as u
join CA_AugME.dbo.sysmembers AS Person
on u.uid = Person.memberuid
JOIN CA_AugME.dbo.sysusers AS a_ROLE
on a_ROLE.uid = groupuid
WHERE U.NAME + A_ROLE.NAME
NOT IN (SELECT U.NAME + A_ROLE.NAME
from CA_SeptME.dbo.sysusers as u
join CA_SeptME.dbo.sysmembers AS Person
on u.uid = Person.memberuid
JOIN CA_SeptME.dbo.sysusers AS a_ROLE
on a_ROLE.uid = groupuid)
-- AND U.NAME = 'jduffy' -- TEST only !

DECLARE @role_name varchar(40)
DECLARE @user_name varchar(40)
OPEN c_from_user_roles

FETCH NEXT FROM c_from_user_roles INTO @user_name, @role_name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
BEGIN
if NOT exists (select * from CA_SeptME.dbo.sysusers where name = @user_name)
begin
exec sp_grantdbaccess @user_name -- add user to DB
-- print 'ADDED USER ' @user_name
SELECT 'ADDED USER ', @user_name
end
END
BEGIN
set @s = 'exec sp_addrolemember ''' + @role_name + ''', ''' + @user_name + ''''
print @s
exec(@s) -- copy Role for user
END
END
FETCH NEXT FROM c_from_user_roles INTO @user_name, @role_name
END

CLOSE c_from_user_roles
DEALLOCATE c_from_user_roles

figaro
figaro
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 126
works great with the function as was posted.

I just added double quotes on the line #92:

set @s = @protect_type_name + ' ' + @action_name + ' ON ' + @object_name + ' TO "' + @to_user + '"'



to support "DOMAIN\user" format.

thanks.
Roberto Figueroa.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search