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

What connection string properties I need to use existing database from App_Data folder?
Alternate to this sql query that will return rows having 0 documents
Get 1 minute interval from start and end field on SQL Server
Delete all records from a table except latest N months records (sorted by id)
UPDLOCK is not avoiding deadlocks why..? [duplicate]
SQL Query /stored procedure to get complete organization hierarchy detail
SQL Server not returning correct output from a user defined function
SQL Server results vary using COALESCE and OR
SSIS dynamic Excel destination not working
How to set data to a variable from temporary table?
How we find Latest Date value and update in another column in Sql?
SQL Replication Running ALTER VIEW against a table
Insert into Table Variable getting Column name or number of supplied values does not match table definition
SSRS set default values to display 1/1/1900 and 1/1/9999
The type of the value being assigned to variable differs from the current variable type
Is it possible to CREATE and USE a SQL Server database in the same query? [duplicate]

Categories

HOME
json
dotnetrdf
answer-set-programming
label
youtube-livestreaming-api
visualization
laravel-5.2
gspread
ext.net
bro
height
facebook-javascript-sdk
lotus-notes
wordpress-theming
gatsby
game-physics
automata
future
mod-pagespeed
sendkeys
spring-cloud-config
ssms-2016
arraylist
plupload
cython
nsmenuitem
profile
jquery-scrollify
spring-test
qsub
bootstrap-typeahead
jflex
auditing
visual-c++-2017
pentaho-report-designer
solidworks
rhomobile
bigcartel
parse-android-sdk
soundjs
rule
common.logging
onbackpressed
web-development-server
code-composer
control-flow-graph
excel-interop
crop
ttcn
php-ews
tableau-server
taskmanager
amazon-machine-learning
webdatagrid
android-bitmap
launch
createobject
iostat
manifest.mf
easing
django-filer
multiple-file-upload
database-backups
multilingual-app-toolkit
post-processing
fill
eml
jta
intellitest
mirrorlink
team-build
prezto
criteria-api
adodb
low-level
candidate-key
mser
em
dalekjs
dache
surrogate-key
picat
lumx
lov
gfs
sqljdbc
android-4.0
socketstream
enumerators
usn
radchart
icefaces-3
deploying
plone-funnelweb
redirectstandardoutput
ou
easygui
idictionary
kpi
lazy-c++
gears
data-entry

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App