sql-server-2008


How to pass User and LogIn as a parameter to a stored procedure?


I have a script that drops the user if it exists and then recreates it. I have this repeated like 30 times. Shown below:
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = N'johndoe')
BEGIN
PRINT 'Dropping user johndoe...'
DROP USER johndoe;
END
CREATE USER [johndoe] FOR LOGIN [uat\johndoe];
I was wondering if it would be possible to write a stored procedure that accepts user name and domain name and then drops and recreates the user?
CREATE PROCEDURE dbo.usp_DropAndRecreateUsers
(#username varchar(100),
#domainName varchar(100))
AS
DECLARE #loginName varchar(200) = '['+#domainName+'\'+#username+']';
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = #username)
BEGIN
DROP USER #username;
END
CREATE USER #username FOR LOGIN #loginName;
so that I could call like:
EXEC usp_DropAndRecreateUsers #username='johndoe', #domainName='uat'
However, after writing a stored procedure, I realized it wouldn't be as easy as I thought. Which probably is the reason this wasn't already done. But still, is this even possible to do in SQL Server (2008 and above)?
What I did was (which doesn't work):
CREATE PROCEDURE dbo.usp_DropAndRecreateUsers
(#username varchar(100),
#domainName varchar(100))
AS
DECLARE #loginName varchar(200) = '['+#domainName+'\'+#username+']';
IF EXISTS (SELECT 1 FROM sys.sysusers AS s WHERE s.name = #username)
BEGIN
DROP USER #username;
END
CREATE USER #username FOR LOGIN #loginName;

Rather than running from variable, create query and execute it


Related Links

How to change row to columns for a single column SQL Server
Pure SQL solution to convert historical DateTime values to DateTimeOffset?
SQL select rows that have one value but not another
VARBINARY(SQLSERVER) to RAW(ORACLE) via CSV created using SQLCMD
How to convert datetime string of different formats in a valid datetime in SQL 2008
Find and replace text in SQL Server
Issue with SSRS Subscriptions
SQL Server 2008 recursive cte string replace maintaining the original value
Updating batch of rows depending on last row of previous batch
Need last value per Year and previous value if null for year
SQL SERVER – Table Missing Identity Values – Insertion in GAP
How to combine multiple “Collect Statistics” statement in openquery
How to break data of columns in multiple rows in SQL
Convert varchar in decimal to time (datetime)
Conversion failed when converting the varchar value to data type int in sql server 2008 new
SQL Server 2008 : Update table records from one database to another database present on two different servers

Categories

HOME
sas
payment-gateway
shopify
wmic
webrtc
google-sheets-api
phaser-framework
avl-tree
unity2d
click
wicket
x11
nixos
cortex-a
flann
gwtp
multiple-monitors
scenebuilder
yeoman-generator
ng-show
mousewheel
plupload
dspic
unpack
cgal
nesc
vertex-buffer
bitcoin-testnet
invoke-command
gpib
piecewise
strophe
pdflatex
salesforce-chatter
google-guava-cache
hashcat
pace
broadleaf-commerce
.net-assembly
inkscape
axis-labels
elfinder
dql
poco-libraries
alchemy.js
android-preferences
paho
jdbi
matlab-cvst
fqdn
w3-total-cache
nsjsonserialization
seamless-immutable
spring-data-hadoop
openh264
cdk
garrys-mod
project-template
objloader
windows-azure-pack
ruby-2.0
webkit2
unsatisfiedlinkerror
logcat
distribute
application-loader
sem
qbfc
pyrocms
viper-architecture
git-checkout
consensus
omnifocus
livechat
snmp4j
method-overriding
system.web
phpdocx
data-generation
dvcs
broadcasting
flash-cc
operations
wic
convex-polygon
onselect
va-list
dig
sharpmap
template-haskell
android-contextmenu
icsharpcode
transitive-closure-table
xgettext
continuous-testing
redirectstandardoutput
expression-evaluation
file-exists
flexicious
serp
easygui
cookieless
rubycas
file-encodings
multibyte-functions
asp.net-mvc-controller
microsoft.ink
inline-if
usergroups





Mobile Apps Dev
Database Users
javascript
java
csharp
php
android


MS Developer
developer works
python
ios
c
html
jquery


RDBMS discuss