Altering the DB Role?

Question

Altering the DB Role?

Chowdary's

SSCrazy

Points: 2978
More actions
September 19, 2014 at 6:31 am

#311637

Hi All,
I have created a login with some restrictions ,By default the Login has DB_Owner Role,
Now i want to change that Role to DB_datareader.
How can i alter the Login role from db_owner to db_datareader
Regards
Chowdary...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You don't alter the role. Users can have many roles. Remove that user from the db_owner role, then add him to the roles you want him to have.

sp_adddbrole and sp_removedbbrole I think the two procs are.

Also, logins do not get db_owner by default. By default they get no roles at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chowdary's SSCrazy Points: 2978 More actions · Answer 2

Thank you for your Reply @Gail,

I am trying to remove the db_owner role to that Login but it is showing an error like

"Cannot Use the Special Principal 'dbo' ",

I cannot able to remove and also not assign the other db role to him same error am getting......

Regards
Chowdary...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What exactly are you doing?

Those aren't roles applied to logins. They are database roles, they are applied to the database user.

Is the login a member of the sysadmin fixed server role? Is that login the owner of the database?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

Chowdary's (9/19/2014)
Thank you for your Reply @Gail,
I am trying to remove the db_owner role to that Login but it is showing an error like
"Cannot Use the Special Principal 'dbo' ",
I cannot able to remove and also not assign the other db role to him same error am getting......

The user owns the database, in this scenario the user maps in via the dbo database user.

Post the results of the following query

SELECTSUSER_SNAME(owner_sid)AS DatabaseOwner

, nameAS DatabaseName

FROM sys.databases

WHERE name = 'yourdb'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Chowdary's SSCrazy Points: 2978 More actions · Answer 5

SELECTSUSER_SNAME(owner_sid)AS DatabaseOwner, name AS DatabaseName
FROM sys.databases
WHERE name = 'yourdb' code]

Hi Perry Whittle..

Here Login Name is :Restricted,

Db Name is :Maintain

PFA....

Regards
Chowdary...

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

So the login "restricted" owns the database "maintain", is this the login you wish to change database access for?

Under sql server 2012, to add a database principal to a role you alter the role as follows

ALTER ROLE somerole ADD MEMBER somedbuser

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉