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

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
How to split string in sql server and put it in a table
Sum until value reached zero
How to compare two columns in two different tables in SQL? [closed]
How to get the Unmatched data from two table
can case statement be applied on a column generated in a select statement?
Invoke SSIS package with 32-bit from enterprise management studio
Complex Case Statement in SSIS Expression
Can anyone help me with SQL in SQL Server
SQL Server 2008 :: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=
Get stored procedure column names
Oracle 11g to SQL Server 2008 Query Conversion

Categories

HOME
swift
vue.js
system-verilog
bing
hana
rfc
orientation
laravel-4
tumblr
browserify
openwrt
constructor
dryioc
viber
pdo
ng-show
jpeg2000
clover
esql
google-ima
multiplayer
qsub
non-deterministic
frp
mayavi
bxslider
roundcube
getjson
svnkit
shapes
modulo
service-locator
sidr
jboss-esb
declare
bayesian-networks
html-agility-pack
catia
context-free-language
startapp
lirc
drawingarea
chunked-encoding
istorage
mouseclick-event
tomee
rmongodb
slam-algorithm
zurb-foundation-apps
pax-web
metalsmith
amf
inotifypropertychanged
usb-drive
geodjango
enyo
prezto
apache-mina
utf-32
line-numbers
odftoolkit
cg
firebug-lite
genetic-programming
outline
yahoo-boss-api
magic-numbers
late-static-binding
pyjade
bfd
osx-snow-leopard
argb
soa-suite
reporting-tools
caliper
userid
supersized
mysql-error-1045
path-manipulation
objectbrowser
easygui
email-spec
recordset
chdatastructures
front-controller
revision
kpi
longjmp

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