0

OK, First it's probably important to state that i'm a noob so please forgive me for any incorrect terminology or misconceptions.

My problem is this, I have a database that I run a restore on periodically and every time I run this restore it resets some user mappings that I have to go in and manually change. I would like to script this change.

This is what I currently have 

    USE profile

    Drop User [<UserName>]
    GO
    Create User [<UserName>] For LOGIN [<UserName>]
    GO
    exec sp_addrolemember 'db_owner', 'UserName'
    GO

This will work for accounts that are actually DataBase user Accounts. Although the account I am trying to fix/modify is actually a master account and is not linked to any database specifically.

Just in case my terminology is confusing i'll explain a little more. If you're looking at the object explorer in SQL Server. The account lives under ServerName>Security>Logins.

Not under ServerName>Database>DatabaseName>Security>Users.

Please let me know if I wasn't clear or more info is needed.

Keifer
  • 39
  • 2
  • 7
  • Are you talking about server roles? – Cade Roux Mar 03 '17 at 17:00
  • https://msdn.microsoft.com/en-us/library/ee677634.aspx - 'ALTER SERVER ROLE' – Cade Roux Mar 03 '17 at 17:00
  • No i dont believe this is a server role. If you're looking at sql server object explore navigate through the skill tree like so ServerName>Security>Logins. Located in the same tree as server roles but not a server role i don't believe. – Keifer Mar 03 '17 at 17:18
  • Those are logins. `CREATE LOGIN`: https://msdn.microsoft.com/en-us/library/ms189751.aspx - Will create server-level logins. Are you trying to add those to roles in the database? – Cade Roux Mar 03 '17 at 17:23
  • So you think create logins should do it? I'm not necessarily trying to create a new login, but rather trying to modify an existing logins "properties" and change the user mapping to have db_owner selected via sql script. I can do this through the ui, but need to write it out as a script to be efficient. – Keifer Mar 03 '17 at 22:10

0 Answers0