!!! Confusion on security logins please assist !!!

Question

!!! Confusion on security logins please assist !!!

SQL_Student

SSCrazy

Points: 2536
More actions
August 29, 2007 at 1:17 am

#69336

SCENARIO: Developers do not have write access on the production databases all there changes needs to go through the DBA who will execute their scripts, but on the one database they have access to create and truncate tables(write access).I went through the logons of the database and their is a user group which has the ddl_admin writes. I know the ddladmin write gives write access as well to the database.
Here is the Confusion: The user group only exists under the DATABASE->USERS tab but does not exist under the SECURITY->LOGIN tab
how is that possible, can that logon still work if not how is it possible for them to write to the database

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You need to create a user in each database the login needs access to, then make that user a member of the necessary role (eg ddl_admin).

John

SQL_Student SSCrazy Points: 2536 More actions · Answer 2

Thanks John for the reply I do know that please check the question.

I'm confused how does the developer have write access to the database, because the only logon which has the ddl_admin role exist in the DATABASE->USER folder but not under the SECURITY->LOGIN tab

So how is it possible for them to create and truncate tables

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 3

- exec sp_helpsrvrolemember @srvrolename = 'sysadmin'

(Is builtin\administrators not removed ?)

- use your_troubled_db

EXEC master..xp_logininfo @acctname = 'youruser',@option = 'all'

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

SQL_Student SSCrazy Points: 2536 More actions · Answer 4

On my developement machine results are

corporate\dba_developer |group user| corporate\dba_developer |NULL

which i agree because the user exists on both DATABASE->USER folder as well as the SECURITY->LOGIN folder

but on the production machine there is no such user the query returns null values

that what is so confusing, it exists under the DATABASE->USER folder but not under the SECURITY->LOGIN folder. So how does it give them access to write to the database.

Thanks alot though your query helped

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 5

Run this for that Database & see what the results it shows

EXEC sp_change_users_login 'Report'

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 6

Which login is the user under Databsae->User mapped to? This does not have to match the Security->Login names at all.

SQL_Student SSCrazy Points: 2536 More actions · Answer 7

To SQLDBA

Executed this command it returns null values EXEC sp_change_users_login 'Report'

Steve

Thanks Steve under database->users: User Name = dba_developer Login Name= <UNKNOWN>

It is UNKNOWN because it does not exist under SECURITY->LOGIN but i dont get how can it still work, where is it mapped to. Do you think it could be a bug in SQL

Johan Bijnens SSC Guru Points: 135361 More actions · Answer 8

- keep in mind sp_change_users_login should be executed with a connection to the troubled database !!

- if it returns no rows, all users are mapped ! So the

- if it returns NULL-values

- if your corporate\dba_developer is member of a windows group that has login-rights to the sqlserver instance, that fact will automaticaly map his login !