change user roles using sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

change user roles using sql

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-11-24 : 10:30:49

Hi,

How would I be able to change users membership on a database using sql? and not manual.

thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 10:36:29

have a look at sp_addrolemember and sp_droprolemember.
And maybe sp_grantdbaccess

Also look in bol see if those have been replaced in v2005 - maybe a create user instead

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-11-24 : 10:46:20

well i already have users setup,

just want to change their roles.

im using:

EXEC sp_droprolemember 'db_owner', 'CAC0028'

but nothing changes. Any ideas?

quote:
Originally posted by nigelrivett

have a look at sp_addrolemember and sp_droprolemember.
And maybe sp_grantdbaccess

Also look in bol see if those have been replaced in v2005 - maybe a create user instead

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 11:01:53

Was CAC0028 in the db_owner role
How do you know nothing has changed? (did you refresh the view if using management studio)

If CAC0028 is sysadmin it won't have any effect.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-11-24 : 11:04:42

well there are several db owners for some reason, like 50 odd.

just want to delete users really using a sp, so a non technical person here can run the sp and just type the user name without having to do anything else.

but the problem is all the users seem to be db_owners.

quote:
Originally posted by nigelrivett

Was CAC0028 in the db_owner role
How do you know nothing has changed? (did you refresh the view if using management studio)

If CAC0028 is sysadmin it won't have any effect.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 11:10:37

There is only one db_owner - have a look in master..sysdatabases
May be many in the db_owner role though and gaining permissions from there.
a sp_droprolemember should remove them - if the user running it has permission.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-11-24 : 11:24:03

ok, got it to work, but giving me an issue with the following statement:

if @username is not null
BEGIN
EXEC sp_droprolemember 'db_owner', @username
END
ELSE
print 'no user'
--
if @username is not null
BEGIN
DROP USER @username
END
ELSE
print 'no user'

its not liking the bottom IF statement.

quote:
Originally posted by nigelrivett

There is only one db_owner - have a look in master..sysdatabases
May be many in the db_owner role though and gaining permissions from there.
a sp_droprolemember should remove them - if the user running it has permission.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 11:29:12

what is the error you are getting?
The user owns objects?
No permission to drop users?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-11-24 : 11:32:33

just says incorrect syntax near @username on the line

DROP USER @username

even though it works when i do drop user 'myuser'

quote:
Originally posted by nigelrivett

what is the error you are getting?
The user owns objects?
No permission to drop users?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 11:36:48

drop user doesn't take a variable

would have to be
declare @sql varchar(1000)
select @sql = 'DROP USER ' + @username
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-11-24 : 11:48:11

i appreciate your help

but now its really annoying,

earlier it worked, now its saying database principal owns a schema in the database, and cannot be dropped
---
declare @username varchar(max), @userid int

select @username= USER_NAME from SGMPC.BPR_USERS
WHERE USER_NAME = 'CAC0032'

select @userid = USER_ID from SGMPC.BPR_USERS
WHERE USER_NAME = 'CAC0032'

if @username is not null
BEGIN

declare @sql varchar(1000)
select @sql = 'sp_droprolemember ' + 'db_owner, ' + @username
exec (@sql)

declare @sql1 varchar(1000)
select @sql1 = 'DROP USER ' + @username
exec (@sql1)

END
---

quote:
Originally posted by nigelrivett

drop user doesn't take a variable

would have to be
declare @sql varchar(1000)
select @sql = 'DROP USER ' + @username
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 11:53:13

drop the schema first.
Was this upgraded from an earlier version? Previously a user and schema were the same thing - now if you upgrade a database a schema with the same name as and owned by the user is created. Objects owned by the user are added to that schema.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-11-24 : 11:56:06

I tested with another user now.

What do you mean by drop the schema?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 12:04:23

execute a drop schema statement - similar to drop user

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-11-24 : 12:34:35

Worked :)

Thanks for that, appreciate it.

quote:
Originally posted by nigelrivett

execute a drop schema statement - similar to drop user

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources