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

Trigger Storing wrong information
Insert Date Range with multiple Id SQL Server 2008
Is float used when you don't know how many digits a number will have?
Stored Procedure call not working Entrity Framework 5 code first
Linked server dynamic catalog for executing MDX through OpenQuery
group rows based on 'date time distance'
Join a table on itself
How to convert select statement to an update statement
Assign customers to shops. Shops have maximum assigned customers
Substring with SQL Server 2008
SQL Update multiple lines from query
SQL Server 2008 vs 2008 R2 for production
'&' character in a text automatically converted to '&' in SQL server while passing via parameter
Combining multiple cte
ZF 2 MS SQL Server error 1934 on update
SQL Server : add a character in a string between 2 comma-separated values

Categories

HOME
udp
signalr
qpython3
avro
specflow
actionscript-2
lotus-notes
oclint
window
xamarin-studio
pjsip
eclipse-luna
sql-update
epicor
memsql
delete-file
bootstrap-typeahead
hash-collision
rhandsontable
metadata-extractor
configure
roundcube
hibernate-cache
exponential
outsystems
skygear
edb
swiftcharts
jboss-esb
nlb
broadleaf-commerce
autocompletetextview
parse-android-sdk
haskell-pipes
android-preferences
bayesian-networks
xbim
stereo-3d
taglib
prototypejs
lumen-5.3
fqdn
production-environment
ti-basic
broadcast
fedora20
selenium-firefoxdriver
collapsingtoolbarlayout
garrys-mod
nsfetchedresultscontrolle
coremidi
iostat
metalsmith
asteriskami
windows-nt
image-editing
spatial-query
asynccallback
datasnap
matcaffe
webhdfs
achartengine
findcontrol
isml
pylearn
android-search
sysctl
omnifocus
odftoolkit
outline
joox
lov
confusion-matrix
goinstant
jqmobi
haskell-platform
justgage
fieldset
domain-calculus
phpsh
ubuntu-11.10
wcf-web-api
isolatedstorage
scraperwiki
datamember
mvccontrib-grid
osx-leopard
yajl
chdatastructures
code-design
swfloader
visual-studio-2010-beta-2
system-codedom-compiler

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